11 Calculating the Fiscal Gap

Code

library(tidyverse)
library(haven)
library(formatR)
library(lubridate)
library(smooth)
library(forecast)
library(scales)
library(kableExtra)
library(ggplot2)
library(readxl)
library(tidyverse)
library(data.table)
library(quantmod)
library(geofacet)
library(janitor)
library(cmapplot)

theme_set(theme_classic() )

knitr::opts_chunk$set(echo = TRUE, warning = FALSE, message = FALSE)

exp_temp <- read_csv("./data/exp_temp.csv")
rev_temp <- read_csv("./data/rev_temp.csv")

11.1 Modify Expenditure File

11.1.1 Tax refunds

Aggregate expenditures: Save tax refunds as negative revenue. Code refunds to match the rev_type codes (02=income taxes, 03 = corporate income taxes, 06=sales tax, 09=motor fuel tax, 24=insurance taxes and fees, 35 = all other tax refunds).

Code

## negative revenue becomes tax refunds

tax_refund_long <- exp_temp %>%           # fund != "0401" # removes State Trust Funds
  filter(fund != "0401" & (object=="9910"|object=="9921"|object=="9923"|object=="9925")) %>%
  # keeps these objects which represent revenue, insurance, treasurer,and financial and professional reg tax refunds
  mutate(refund = case_when(
    fund=="0278" & sequence == "00" ~ "02", # for income tax refund
    fund=="0278" & sequence == "01" ~ "03", # tax administration and enforcement and tax operations become corporate income tax refund
     fund == "0278" & sequence == "02" ~ "02",
    object=="9921" ~ "21",                # inheritance tax and estate tax refund appropriation
    object=="9923" ~ "09",                # motor fuel tax refunds
    obj_seq_type == "99250055" ~ "06",    # sales tax refund
    fund=="0378" & object=="9925" ~ "24", # insurance privilege tax refund
   (fund=="0001" & object=="9925") | (object=="9925" & fund == "0384" & fy == 2023) ~ "35", # all other taxes
   # fund=="0001" & object=="9925" ~ "35", # all other taxes
      T ~ "CHECK"))                       # if none of the items above apply to the observations, then code them as CHECK 

    
exp_temp <- left_join(exp_temp, tax_refund_long) %>%
  mutate(refund = ifelse(is.na(refund),"not refund", as.character(refund)))

tax_refund <- tax_refund_long %>% 
  group_by(refund, fy)%>%
  summarize(refund_amount = sum(expenditure, na.rm = TRUE)/1000000) %>%
  pivot_wider(names_from = refund, values_from = refund_amount, names_prefix = "ref_") %>%
  mutate_all(~replace_na(.,0)) %>%
  arrange(fy)

tax_refund %>% 
  pivot_longer( ref_02:ref_35, names_to = "Refund Type", values_to = "Amount") %>%
  ggplot()+
  theme_classic()+
  geom_line(aes(x=fy,y=Amount, group = `Refund Type`, color = `Refund Type`))+
  labs(title = "Refund Types", 
       caption = "Refunds are excluded from Expenditure totals and instead subtracted from Revenue totals") + 
  labs(title = "Tax refunds", 
       caption = "Rev_type codes: 02=income taxes, 03=corporate income taxes, 06=sales tax, 09=motor fuel tax, 
       24=insurance taxes and fees, 35 = all other tax refunds." )

# remove the items we recoded in tax_refund_long
exp_temp <- exp_temp %>% filter(refund == "not refund")

tax_refund amounts are removed from expenditure totals and subtracted from revenue totals (since they were tax refunds).

11.1.2 Pension Expenditures

State pension contributions are largely captured with object=4431. (State payments into pension fund). State payments to the following pension systems:

Teachers Retirement System (TRS)
New POB bond in 2019: Accelerated Bond Fund paid benefits in advance as lump sum
State Employee Retirement System (SERS)
State University Retirement System (SURS)
Judges Retirement System (JRS)
General Assembly Retirement System (GARS)

Modify exp_temp and move all state pension contributions to their own group (901). For more information on the variables included or excluded, please see Chapter 6.

Code

exp_temp <-  exp_temp %>% 
  arrange(fund) %>%
  mutate(pension = case_when( 
    (object=="4431") ~ 1, # 4431 = easy to find pension payments INTO fund
    
    # (object>"1159" & object<"1166") & fund != "0183" & fund != "0193"   ~ 2, 
    # objects 1159 to 1166 are all considered Retirement by Comptroller, 
    # Excluded - employer contributions from agencies/organizations/etc.
    
    (object=="1298" &  # Purchase of Investments, Normally excluded
       (fy==2010 | fy==2011) & 
       (fund=="0477" | fund=="0479" | fund=="0481")) ~ 3, #judges retirement OUT of fund
    # state borrowed money from pension funds to pay for core services during 2010 and 2011. 
    # used to fill budget gap and push problems to the future. 
    
    fund == "0319" ~ 4, # pension stabilization fund
    TRUE ~ 0) )

table(exp_temp$pension)


     0      1      3      4 
237580    242      6     15

Code

exp_temp %>% 
  filter(pension != 0) %>%
  mutate(pension = as.factor(pension))%>%
  group_by(fy, pension) %>% 
  summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>%
  ggplot(aes(x=fy, y = expenditure, group=pension)) + 
  theme_classic()+
  geom_col(aes(fill = pension)) + 

  labs (title = "Pension expenditures", 
  caption = "1 = State contributions INTO pension funds. 
  3 = Purchase of Investments anomoly in 2010 and 2011. 
  4 = pension stabilization fund")+
    theme(legend.position = "bottom")

Code

# special accounting of pension obligation bond (POB)-funded contributions to JRS, SERS, GARS, TRS 

exp_temp <- exp_temp %>% 
  # change object for 2010 and 2011, retirement expenditures were bond proceeds and would have been excluded
  mutate(object = ifelse((pension >0 & in_ff == "0"), "4431", object)) %>% 
  # changes weird teacher & judge retirement system  pensions object to normal pension object 4431
  mutate(pension =  ifelse(pension >0 & in_ff == "0", 6, pension)) %>% # coded as 6 if it was supposed to be excluded. 
  mutate(in_ff = ifelse(pension>0, "1", in_ff))

table(exp_temp$pension)


     0      1      4      6 
237580    240     15      8

Code

# all other pensions objects  codes get agency code 901 for State Pension Contributions
exp_temp <- exp_temp %>% 
  mutate(agency = ifelse(pension>0, "901", as.character(agency)),
         agency_name = ifelse(agency == "901", "State Pension Contributions", as.character(agency_name)))

exp_temp %>% 
 filter(pension > 0) %>%  
  mutate(pension = as.factor(pension)) %>%
  group_by(fy, pension) %>% 
  summarize(expenditure = sum(expenditure, na.rm=TRUE)) %>%
  ggplot(aes(x=fy, y=expenditure, color = pension)) +
  geom_line() + 
  theme_classic()+
  labs (title = "Pension Expenditures", 
  caption = "")

exp_temp %>% 
 filter(pension > 0) %>%  
  group_by(fy) %>% 
  summarize(expenditure = sum(expenditure, na.rm=TRUE)) %>%
  ggplot(aes(x=fy, y=expenditure)) +
  geom_line() + 
  theme_classic()+
  labs (title = "Pension Expenditures")

11.1.3 Drop Interfund transfers

Drop all cash transfers between funds, statutory transfers, and purchases of investments from expenditure data.

object == 1993 is for interfund cash transfers
agency == 799 is for statutory transfers
object == 1298 is for purchase of investments and is not spending EXCEPT for costs in 2010 and 2011 (and were recoded already to object == “4431”). Over 168,000 observations remain.
- 153,889 observations on 1/23/2022?

Code

transfers_drop <- exp_temp %>% filter(
  agency == "799" | # statutory transfers
           object == "1993" |  # interfund cash transfers
           object == "1298") # purchase of investments
transfers_drop # items being dropped,

Code

# always check to make sure you aren't accidently dropping something of interest.

exp_temp <- anti_join(exp_temp, transfers_drop)
exp_temp

11.1.4 State employee healthcare costs

Coding healthcare costs was quite difficult. Over the years, State employee healthcare has been within Central Management Bureau of Benefits and Healthcare & Family Services.

If observation is a group insurance contribution, then the expenditure amount is set to $0 (essentially dropped from analysis).

Agency 416 had group insurance contributions for 1998-2005 and 2013-present. Agency 478 had group insurance contributions from 2006-2012.

FY2021 and FY2022 contributions coded with object = 1900 (lump sum) for some reason??

Code

#if observation is a group insurance contribution, then the expenditure amount is set to $0 (essentially dropped from analysis)

# pretend eehc is named group_insurance_contribution or something like that
# eehc coded as zero implies that it is group insurance
# if eehc=0, then expenditures are coded as zero for group insurance to avoid double counting costs


exp_temp <- exp_temp %>% 
  mutate(eehc = ifelse(
    # group insurance contributions for 1998-2005 and 2013-present
   fund == "0001" & (object == "1180" | object =="1900") & agency == "416" & appr_org=="20", 0, 1) )%>% 
  
  mutate(eehc = ifelse(
    # group insurance contributions for 2006-2012
    fund == "0001" & object == "1180" & agency == "478" 
    & appr_org=="80", 0, eehc) )%>%
    
   # group insurance contributions from road fund
  # coded with 1900 for some reason??
    mutate(eehc = ifelse(
      fund == "0011" & object == "1900" & 
        agency == "416" & appr_org=="20", 0, eehc) ) %>%
  
  mutate(expenditure = ifelse(eehc=="0", 0, expenditure)) %>%
  
  mutate(agency = case_when(   # turns specific items into State Employee Healthcare (agency=904)
      fund=="0907" & (agency=="416" & appr_org=="20") ~ "904",   # central management Bureau of benefits using health insurance reserve 
      fund=="0907" & (agency=="478" & appr_org=="80") ~ "904",   # agency = 478: healthcare & family services using health insurance reserve - stopped using this in 2012
      TRUE ~ as.character(agency))) %>%
  mutate(agency_name = ifelse(
    agency == "904", "STATE EMPLOYEE HEALTHCARE", as.character(agency_name)),
         in_ff = ifelse( agency == "904", 1, in_ff),
         group = ifelse(agency == "904", "904", as.character(agency)))  
# creates group variable

# Default group = agency number

healthcare_costs <- exp_temp %>% filter(group == "904")

healthcare_costs

Code

exp_temp %>% 
  filter(group == "904") %>% 
  group_by(fy) %>% 
  summarise(healthcare_cost = sum(expenditure, na.rm = TRUE)) %>% 
  ggplot() +
  geom_line(aes(x=fy, y=healthcare_cost)) + 
  labs(title="State Employee Healthcare Costs - Included in Fiscal Futures Model", 
       caption = "Fund 0907 for agencies responsible for health insurance reserve (DHFS & CMS)")

Code

#exp_temp <- anti_join(exp_temp, healthcare_costs) %>% mutate(expenditure = ifelse(object == "1180", 0, expenditure))

#healthcare_costs_yearly <- healthcare_costs %>% group_by(fy, group) %>% summarise(healthcare_cost = sum(expenditure, na.rm = TRUE)/1000000) %>% select(-group)

11.1.5 Local Transfers

Separate transfers to local from parent agencies that come from DOR(492) or Transportation (494). Treats muni revenue transfers as expenditures, not negative revenue.

The share of certain taxes levied state-wide at a common rate and then transferred to local governments. (Purely local-option taxes levied by specific local governments with the state acting as collection agent are NOT included.)

The six corresponding revenue items are:

Local share of Personal Income Tax
- Individual Income Tax Pass-Through New 2021 (source 2582).
Local share of General Sales Tax
Personal Property Replacement Tax on Business Income
Personal Property Replacement Tax on Public Utilities
Local share of Motor Fuel Tax
Transportation Renewal Fund 0952

Until Dec 18. 2022, Local CURE was being aggregated into Revenue totals since the agency was the Department of Revenue. However the $371 million expenditure is for “LOC GOVT ARPA” and the revenue source that is Local CURE is also $371 million. Since it cancels out and is just passed through the state government, I am changing changing the fund_ab_in file so that in_ff=0 for the Local CURE fund. It also inflates the department of revenue expenditures in a misleading way when the expense is actually a transfer to local governments.

Dropping Local CURE fund from analysis results in a $371 million decrease in the department of Revenue (where the Local Government ARPA transfer money). The appropriation for it was over $740 million so some will probably be rolled over to FY23 too.
In the FY21 New and Reused Funds word document, 0325 Local CURE is described as “Created as a federal trust fund. The fund is established to receive transfers from either the disaster response and recovery fund or the state cure fund of federal funds received by the state. These transfers, subject to appropriation, will provide for the administration and payment of grants and expense reimbursements to units of local government. Revenues should be under Federal Other and expenditures under Commerce and Economic Opportunity.” - I propose changing it to exclude for both.

Code

exp_temp <- exp_temp %>% mutate(
  agency = case_when(fund=="0515" & object=="4470" & type=="08" ~ "971", # income tax to local governments
                     fund=="0515" & object=="4491" & type=="08" & sequence=="00" ~ "971", # object is shared revenue payments
                     fund=="0802" & object=="4491" ~ "972", #pprt transfer
                     fund=="0515" & object=="4491" & type=="08" & sequence=="01" ~ "976", #gst to local
                     fund=="0627" & object=="4472"~ "976" , # public transportation fund but no observations exist
                     fund=="0648" & object=="4472" ~ "976", # downstate public transportation, but doesn't exist
                     fund=="0515" & object=="4470" & type=="00" ~ "976", # object 4470 is grants to local governments
                    object=="4491" & (fund=="0188"|fund=="0189") ~ "976",
                     fund=="0187" & object=="4470" ~ "976",
                     fund=="0186" & object=="4470" ~ "976",
                    object=="4491" & (fund=="0413"|fund=="0414"|fund=="0415")  ~ "975", #mft to local
                  fund == "0952"~ "975", # Added Sept 29 2022 AWM. Transportation Renewal MFT
                    TRUE ~ as.character(agency)),
  
  agency_name = case_when(agency == "971"~ "INCOME TAX 1/10 TO LOCAL",
                          agency == "972" ~ "PPRT TRANSFER TO LOCAL",
                          agency == "975" ~ "MFT TO LOCAL",
                          agency == "976" ~ "GST TO LOCAL",
                          TRUE~as.character(agency_name)),
  group = ifelse(agency>"970" & agency < "977", as.character(agency), as.character(group)))

Code

transfers_long <- exp_temp %>% 
  filter(group == "971" |group == "972" | group == "975" | group == "976")

transfers_long %>% 
  group_by(agency_name, group, fy) %>% 
  summarize(expenditure = sum(expenditure, na.rm=TRUE) )%>% 
  ggplot() + 
  geom_line(aes(x=fy, y = expenditure, color=agency_name)) + 
  theme_classic()+
  theme(legend.position = "bottom", legend.title=element_blank())+
  labs(title = "Transfers to Local Governments", 
       caption = "Data Source: Illinois Office of the Comptroller")

transfers <- transfers_long %>%
  group_by(fy, group ) %>%
  summarize(sum_expenditure = sum(expenditure)/1000000) %>%
  pivot_wider(names_from = "group", values_from = "sum_expenditure", names_prefix = "exp_" )

exp_temp <- anti_join(exp_temp, transfers_long)


dropped_inff_0 <- exp_temp %>% filter(in_ff == 0)

exp_temp <- exp_temp %>% filter(in_ff == 1) # drops in_ff = 0 funds AFTER dealing with net-revenue above

Figure 11.5: Drop Transfers from State to Local Governments

The Local Transfers from the Personal Property Replacement Tax (fund 802) increased over $2 billion from corporate income taxes alone. Personal property replacement taxes (PPRT) are revenues collected by the state of Illinois and paid to local governments to replace money that was lost by local governments when their powers to impose personal property taxes on corporations, partnerships, and other business entities were taken away.

11.1.6 Debt Service

Debt Service expenditures include interest payment on both short-term and long-term debt. We do not include escrow or principal payments.

Decision from Sept 30 2022: We are no longer including short term principal payments as a cost; only interest on borrowing is a cost. Pre FY22 and the FY21 correction, we did include an escrow payment and principle payments as costs but not bond proceeds as revenues. This caused expenditures to be inflated because we were essentially counting debt twice - the principle payment and whatever the money was spent on in other expenditure categories, which was incorrect.

Code

debt_drop <- exp_temp %>% 
  filter(object == "8841" |  object == "8811")  
# escrow  OR  principle

#debt_drop %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy)


debt_keep <- exp_temp %>% 
  filter(fund != "0455" & (object == "8813" | object == "8800" )) 
# examine the debt costs we want to include

#debt_keep %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy) 


exp_temp <- anti_join(exp_temp, debt_drop) 
exp_temp <- anti_join(exp_temp, debt_keep)

debt_keep <- debt_keep %>%
  mutate(
    agency = ifelse(fund != "0455" & (object == "8813" | object == "8800"), "903", as.character(agency)),
    group = ifelse(fund != "0455" & (object == "8813" | object == "8800"), "903", as.character(group)),
    in_ff = ifelse(group == "903", 1, as.character(in_ff)))

debt_keep_yearly <- debt_keep %>% group_by(fy, group) %>% summarize(debt_cost = sum(expenditure,na.rm=TRUE)/1000000) %>% select(-group)

11.1.7 Medicaid

Medicaid. That portion of the Healthcare and Family Services (or Public Aid in earlier years, agency code 478) budget for Medical (appr_organization code 65) for awards and grants (object codes 4400 and 4900).

State CURE will remain in the Medicaid expenditure category due to the nature of it being federal funds providing public health services and funding to locations that provide public services.

Uses same appropriation name of “HEALTHCARE PROVIDER RELIEF” and fund == 0793 and obj_seq_type == 49000000. So can defend the “mistake” of including healthcare provider relief as Medicaid expenditure.

11.1.8 Add Other Fiscal Future group codes

Code

exp_temp <- exp_temp %>%
  #mutate(agency = as.numeric(agency) ) %>%
  # arrange(agency)%>%
  mutate(
    group = case_when(
      agency>"100"& agency<"200" ~ "910", # legislative
      
      agency == "528"  | (agency>"200" & agency<"300") ~ "920", # judicial
      pension>0  ~ "901", # pensions
      (agency>"309" & agency<"400") ~ "930",    # elected officers
      
      agency == "586" ~ "959", # create new K-12 group

      agency=="402" | agency=="418" | agency=="478" | agency=="444" | agency=="482" ~ as.character(agency), # aging, CFS, HFS, human services, public health
      T ~ as.character(group))
    ) %>%      

  
  mutate(group = case_when(
    agency=="478" & (appr_org=="01" | appr_org == "65" | appr_org=="88") & (object=="4900" | object=="4400") ~ "945", # separates CHIP from health and human services and saves it as Medicaid
    
    agency == "586" & fund == "0355" ~ "945",  # 586 (Board of Edu) has special education which is part of medicaid
    
    # OLD CODE: agency == "586" & appr_org == "18" ~ "945", # Spec. Edu Medicaid Matching
    
    agency=="425" | agency=="466" | agency=="546" | agency=="569" | agency=="578" | agency=="583" | agency=="591" | agency=="592" | agency=="493" | agency=="588" ~ "941", # public safety & Corrections
    
    agency=="420" | agency=="494" |  agency=="406" | agency=="557" ~ as.character(agency), # econ devt & infra, tollway
    
    agency=="511" | agency=="554" | agency=="574" | agency=="598" ~ "946",  # Capital improvement
    
    agency=="422" | agency=="532" ~ as.character(agency), # environment & nat. resources
    
    agency=="440" | agency=="446" | agency=="524" | agency=="563"  ~ "944", # business regulation
    
    agency=="492" ~ "492", # revenue
    
    agency == "416" ~ "416", # central management services
    agency=="448" & fy > 2016 ~ "416", #add DoIT to central management 
    
    T ~ as.character(group))) %>%
  
  
  mutate(group = case_when(
    # agency=="684" | agency=="691"  ~ as.character(agency), # moved under higher education in next line. 11/28/2022 AWM
    
    agency=="692" | agency=="695" | agency == "684" |agency == "691" | (agency>"599" & agency<"677") ~ "960", # higher education
    
    agency=="427"  ~ as.character(agency), # employment security
    
    agency=="507"|  agency=="442" | agency=="445" | agency=="452" |agency=="458" | agency=="497" ~ "948", # other departments
    
    # other boards & Commissions
    agency=="503" | agency=="509" | agency=="510" | agency=="565" |agency=="517" | agency=="525" | agency=="526" | agency=="529" | agency=="537" | agency=="541" | agency=="542" | agency=="548" |  agency=="555" | agency=="558" | agency=="559" | agency=="562" | agency=="564" | agency=="568" | agency=="579" | agency=="580" | agency=="587" | agency=="590" | agency=="527" | agency=="585" | agency=="567" | agency=="571" | agency=="575" | agency=="540" | agency=="576" | agency=="564" | agency=="534" | agency=="520" | agency=="506" | agency == "533" ~ "949", 
    
    # non-pension expenditures of retirement funds moved to "Other Departments"
    # should have removed pension expenditures already from exp_temp in Pensions step above
    agency=="131" | agency=="275" | agency=="589" |agency=="593"|agency=="594"|agency=="693" ~ "948",
    
    T ~ as.character(group))) %>%

  mutate(group_name = 
           case_when(
             group == "416" ~ "Central Management",
             group == "478" ~ "Healthcare and Family Services",
             group == "482" ~ "Public Health",
             group == "900" ~ "NOT IN FRAME",
             group == "901" ~ "STATE PENSION CONTRIBUTION",
             group == "903" ~ "DEBT SERVICE",
             group == "910" ~ "LEGISLATIVE"  ,
             group == "920" ~ "JUDICIAL" ,
             group == "930" ~ "ELECTED OFFICERS" , 
             group == "940" ~ "OTHER HEALTH-RELATED", 
             group == "941" ~ "PUBLIC SAFETY" ,
             group == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
             group == "943" ~ "CENTRAL SERVICES",
             group == "944" ~ "BUS & PROFESSION REGULATION" ,
             group == "945" ~ "MEDICAID" ,
             group == "946" ~ "CAPITAL IMPROVEMENT" , 
             group == "948" ~ "OTHER DEPARTMENTS" ,
             group == "949" ~ "OTHER BOARDS & COMMISSIONS" ,
             group == "959" ~ "K-12 EDUCATION" ,
             group == "960" ~ "UNIVERSITY EDUCATION" ,
             group == agency ~ as.character(group),
             TRUE ~ "Check name"),
         year = fy)

exp_temp %>% filter(group_name == "Check name")

Code

#write_csv(exp_temp, "all_expenditures_recoded.csv")

Important

All expenditures recoded but not aggregated: Allows for inspection of individual expenditures within larger categories. This stage of the data is extremely useful for investigating how individual items have been coded before they are aggregated into larger categories.

11.2 Modify Revenue data

Revenue Categories NOT included in Fiscal Futures:
- 32. Garnishment-Levies. (State is fiduciary, not beneficiary.)
- 45. Student Fees-Universities. (Excluded from state-level budget.)
- 51. Retirement Contributions (of individuals and non-state entities).
- 66. Proceeds, Investment Maturities. (Not sustainable flow.)
- 72. Bond Issue Proceeds. (Not sustainable flow.)
- 75. Inter-Agency Receipts.
- 79. Cook County Intergovernmental Transfers. (State is not beneficiary.)
- 98. Prior Year Refunds.
- 99. Statutory Transfers.

All Other Sources

Expanded to include the following smaller sources:
- 30. Horse Racing Taxes & Fees.
- 60. Other Grants and Contracts.
- 63. Investment Income.

For aggregating revenue, use the rev_1998_2022 dataframe, join the funds_ab_in_2022 file to it, and then join the ioc_source_type file to the dataset. Remember: You need to update the funds_ab_in and ioc_source_type file every year!

Code

# recodes old agency numbers to consistent agency number
rev_temp <- rev_temp %>% 
  mutate(agency = case_when(
    (agency=="438"| agency=="475" |agency == "505") ~ "440",
    # financial institution &  professional regulation &
     # banks and real estate  --> coded as  financial and professional reg
    agency == "473" ~ "588", # nuclear safety moved into IEMA
    (agency =="531" | agency =="577") ~ "532", # coded as EPA
    (agency =="556" | agency == "538") ~ "406", # coded as agriculture
    agency == "560" ~ "592", # IL finance authority (fire trucks and agriculture stuff)to state fire marshal
    agency == "570" & fund == "0011" ~ "494",   # city of Chicago road fund to transportation
    TRUE ~ (as.character(agency))))

11.2.1 Federal to State Transfers

For an deeper look at federal revenue to Illinois, Chapter 3.

Code

#rev_temp <- rev_temp %>% filter(in_ff==1)

rev_temp <- rev_temp %>% 
  mutate(
    rev_type = ifelse(rev_type=="57" & agency=="478" & (source=="0618"|source=="2364"|source=="0660"|source=="1552"| source=="2306"| source=="2076"|source=="0676"|source=="0692"), "58", rev_type),
    rev_type_name = ifelse(rev_type=="58", "Federal Medicaid Reimbursements", rev_type_name),
    rev_type = ifelse(rev_type=="57" & agency=="494", "59", rev_type),
    rev_type_name = ifelse(rev_type=="59", "Federal Transportation", rev_type_name),
    rev_type_name = ifelse(rev_type=="57", "Federal - Other", rev_type_name),
    rev_type = ifelse(rev_type=="6", "06", rev_type),
    rev_type = ifelse(rev_type=="9", "09", rev_type)) 

rev_temp %>% 
  filter(rev_type == "58" | rev_type == "59" | rev_type == "57") %>% 
  group_by(fy, rev_type, rev_type_name) %>% 
  summarise(receipts = sum(receipts, na.rm = TRUE)/1000000) %>% 
  ggplot() +
      geom_recessions(xformay = "numeric",text = FALSE)+
  geom_line(aes(x=fy, y=receipts,color=rev_type_name)) +
      theme_classic() +
  scale_y_continuous(labels = comma)+
  labs(title = "Federal to State Transfers", 
       y = "Millions of Dollars", x = "") + 
  theme(legend.position = "bottom", legend.title = element_blank()  )

Dropping State CURE Revenue

The Fiscal Futures model focuses on sustainable revenue sources. To understand our fiscal gap and outlook, we need to exclude these one time revenues. GOMB has emphasized that they have allocated COVID dollars to one time expenditures (unemployment trust fund, budget stabilization fund, etc.). The fiscal gap, graphs,and CAGRs have been recalculated in the [Drop COVID Dollars] section below.

NOTE: The code chunk below only drops revenue sources with the source name of “Federal Stimulus Package” (which is the State and Local CURE revenue). Additional federal money went into other funds during the beginning of pandemic. Many departments saw increased grants and received other funds (e.g. funds)

Code

rev_temp <- rev_temp %>% mutate(covid_dollars = ifelse(source_name_AWM == "FEDERAL STIMULUS PACKAGE",1,0))

rev_temp %>% filter(source_name_AWM == "FEDERAL STIMULUS PACKAGE") %>%
  group_by(fy) %>% summarize(Received = sum(receipts))

11.2.2 Health Insurance Premiums from Employees

Insurance premiums for employees is coded below but it is NOT used in the fiscal futures model. Employee and employer premiums are considered rev_51 and dropped from analysis in later step.

0120 = ins prem-option life
0120 = ins prem-optional life/univ
0347 = optional health - HMO
0348 = optional health - dental
0349 = optional health - univ/local SI
0350 = optional health - univ/local
0351 = optional health - retirement
0352 = optional health - retirement SI
0353 = optional health - retire/dental
0354 = optional health - retirement hmo
2199-2209 = various HMOs, dental, health plans from Health Insurance Reserve (fund)

Code

#collect optional insurance premiums to fund 0907 for use in eehc expenditure  
rev_temp <- rev_temp %>% 
  mutate(
    #variable not used in aggregates, but could be interesting for other purposes
    employee_premiums = ifelse(fund=="0907" & (source=="0120"| source=="0121"| (source>"0345" & source<"0357")|(source>"2199" & source<"2209")), 1, 0),
    
    # adds more rev_type codes
    rev_type = case_when(
      fund =="0427" ~ "12", # pub utility tax
      fund == "0742" | fund == "0473" ~ "24", # insurance and fees
      fund == "0976" ~ "36",# receipts from rev producing
      fund == "0392" |fund == "0723" ~ "39", # licenses and fees
      fund == "0656" ~ "78", #all other rev sources
      TRUE ~ as.character(rev_type)))
# if not mentioned, then rev_type as it was



# # optional insurance premiums = employee insurance premiums

# emp_premium <- rev_temp %>%
#   group_by(fy, employee_premiums) %>%
#   summarize(employee_premiums_sum = sum(receipts)/1000000) %>%
#   filter(employee_premiums == 1) %>%
#   rename(year = fy) %>% 
#   select(-employee_premiums)

emp_premium_long <- rev_temp %>%  filter(employee_premiums == 1)
# 381 observations have employee premiums == 1


# drops employee premiums from revenue
# rev_temp <- rev_temp %>% filter(employee_premiums != 1)
# should be dropped in next step since rev_type = 51

Note: In FY21, employee premiums were subtracted from state healthcare costs on the expenditure side to calculate a “Net Healthcare Cost” but that methodology has been discontinued. Totals were practically unchanged: revenue from employee premiums is also very small.

11.2.3 Transfers in and Out:

Funds that hold and disperse local taxes or fees are dropped from the analysis. Then other excluded revenue types are also dropped.

Drops Blank, Student Fees, Retirement contributions, proceeds/investments, bond issue proceeds, interagency receipts, cook IGT, Prior year refunds:

Code

rev_temp <- rev_temp %>% 
  filter(in_ff == 1) %>% 
  mutate(local = ifelse(is.na(local), 0, local)) %>% # drops all revenue observations that were coded as "local == 1"
  filter(local != 1)

# 1175 doesnt exist?
in_from_out <- c("0847", "0867", "1175", "1176", "1177", "1178", "1181", "1182", "1582", "1592", "1745", "1982", "2174", "2264")

# what does this actually include:
# all are items with rev_type = 75 originally. 
in_out_df <- rev_temp %>%
  mutate(infromout = ifelse(source %in% in_from_out, 1, 0)) %>%
  filter(infromout == 1)

rev_temp <- rev_temp %>% 
  mutate(rev_type_new = ifelse(source %in% in_from_out, "76", rev_type))
# if source contains any of the codes in in_from_out, code them as 76 (all other rev).
# I end up excluding rev_76 in later steps

Code

# revenue types to drop
drop_type <- c("32", "45", "51", 
               "66", "72", "75", "79", "98")

# drops Blank, Student Fees, Retirement contributions, proceeds/investments,
# bond issue proceeds, interagency receipts, cook IGT, Prior year refunds.


rev_temp <- rev_temp %>% filter(!rev_type_new %in% drop_type)
# keep observations that do not have a revenue type mentioned in drop_type

table(rev_temp$rev_type_new)


   02    03    06    09    12    15    18    21    24    27    30    31    33 
  177   132   852   134   593   269    47  1421   473    79   665   130   141 
   35    36    39    42    48    54    57    58    59    60    63    76    78 
  691  5328  9487  2939    32  1283  6683   646   233   105  5321   159 11712 
   99 
 1115

Code

rev_temp %>% 
  group_by(fy, rev_type_new) %>% 
  summarize(total_reciepts = sum(receipts)/1000000) %>%
  pivot_wider(names_from = rev_type_new, values_from = total_reciepts, names_prefix = "rev_")

Code

# combines smallest 4  categories to to "Other"
# they were the 4 smallest in past years, are they still the 4 smallest? 

rev_temp <- rev_temp %>%  
 mutate(rev_type_new = ifelse(rev_type=="30" | rev_type=="60" | rev_type=="63" | rev_type=="76", "78", rev_type_new))


#table(rev_temp$rev_type_new)  # check work



rm(rev_1998_2022)
rm(exp_1998_2022)


#write.csv(exp_temp, "exp_fy22_recoded_12192022.csv")
#write.csv(rev_temp, "rev_fy22_recoded_12192022.csv")

11.3 Pivoting and Merging

Local Government Transfers (exp_970) should be on the expenditure side

11.3.1 Revenues

I chose to drop rev_76 for Transfers in and Out because I do not understand why that step occurs in the previously used Stata code. Rev_76 was created and included in rev_78 for All Other Revenues in old Stata code for years before FY21 but that method has been discontinued for FY22. Including vs excluding rev_76 does not change the overall interpretation of the fiscal gap.

Code

ff_rev <- rev_temp %>% 
  group_by(rev_type_new, fy) %>% 
  summarize(sum_receipts = sum(receipts, na.rm=TRUE)/1000000 ) %>%
  pivot_wider(names_from = "rev_type_new", values_from = "sum_receipts", names_prefix = "rev_")

ff_rev<- left_join(ff_rev, tax_refund)

#ff_rev <- left_join(ff_rev, pension2_fy22, by=c("fy" = "year"))

#ff_rev <- left_join(ff_rev, eehc2_amt) 
ff_rev <- mutate_all(ff_rev, ~replace_na(.,0))

# 
# ff_rev <- ff_rev %>%
#   mutate(rev_02 = rev_02 - ref_02,
#          rev_03 = rev_03 - ref_03,
#          rev_06 = rev_06 - ref_06,
#          rev_09 = rev_09 - ref_09,
#          rev_21 = rev_21 - ref_21,
#          rev_24 = rev_24 - ref_24,
#          rev_35 = rev_35 - ref_35
# 
#       #   rev_78new = rev_78 #+ pension_amt #+ eehc
#          ) %>% 
#   select(-c(ref_02:ref_35, rev_99, rev_NA, rev_76
#             #, ref_CHECK#, pension_amt , rev_76,
#           #  , eehc
#             ))
# 
# ff_rev



#noproblem <- c(0)  # if ref_CHECK = $0, then there is no problem. :) 

if((sum(ff_rev$ref_CHECK) == 0 )){

ff_rev <- ff_rev %>%
  
  mutate(rev_02 = rev_02 - ref_02,
         rev_03 = rev_03 - ref_03,
         rev_06 = rev_06 - ref_06,
         rev_09 = rev_09 - ref_09,
         rev_21 = rev_21 - ref_21,
         rev_24 = rev_24 - ref_24,
         rev_35 = rev_35 - ref_35
         ) %>% 
  select(-c(ref_02:ref_35, rev_99, rev_76, ref_CHECK )) 
}else{"You have a problem! Check what revenue items did not have rev codes (causing it to be coded as rev_NA) or the check if there were refunds that were not assigned revenue codes (tax_refunds_long objects)"}

ff_rev %>%  mutate_all(., ~round(.,digits=0))

Table 11.1:

Pivoted Revenue Table ($ Millions) - Intermediate Step

Since I already pivot_wider()ed the table in the previous code chunk, I now change each column’s name by using rename() to set new variable names. Ideally the final dataframe would have both the variable name and the variable label but I have not done that yet.

Code

aggregate_rev_labels <- ff_rev %>%
  rename("INDIVIDUAL INCOME TAXES, gross of local, net of refunds" = rev_02,
         "CORPORATE INCOME TAXES, gross of PPRT, net of refunds" = rev_03,
         "SALES TAXES, gross of local share" = rev_06 ,
         "MOTOR FUEL TAX, gross of local share, net of refunds" = rev_09 ,
         "PUBLIC UTILITY TAXES, gross of PPRT" = rev_12,
         "CIGARETTE TAXES" = rev_15 ,
         "LIQUOR GALLONAGE TAXES" = rev_18,
         "INHERITANCE TAX" = rev_21,
         "INSURANCE TAXES&FEES&LICENSES, net of refunds" = rev_24 ,
         "CORP FRANCHISE TAXES & FEES" = rev_27,
       # "HORSE RACING TAXES & FEES" = rev_30,  # in Other
         "MEDICAL PROVIDER ASSESSMENTS" = rev_31 ,
         # "GARNISHMENT-LEVIES " = rev_32 , # dropped
         "LOTTERY RECEIPTS" = rev_33 ,
         "OTHER TAXES" = rev_35,
         "RECEIPTS FROM REVENUE PRODUCNG" = rev_36, 
         "LICENSES, FEES & REGISTRATIONS" = rev_39 ,
         "MOTOR VEHICLE AND OPERATORS" = rev_42 ,
         #  "STUDENT FEES-UNIVERSITIES" = rev_45,   # dropped
         "RIVERBOAT WAGERING TAXES" = rev_48 ,
         # "RETIREMENT CONTRIBUTIONS " = rev_51, # dropped
         "GIFTS AND BEQUESTS" = rev_54, 
         "FEDERAL OTHER" = rev_57 ,
         "FEDERAL MEDICAID" = rev_58, 
         "FEDERAL TRANSPORTATION" = rev_59 ,
         #"OTHER GRANTS AND CONTRACTS" = rev_60, #other
       # "INVESTMENT INCOME" = rev_63, # other
         # "PROCEEDS,INVESTMENT MATURITIES" = rev_66 , #dropped
         # "BOND ISSUE PROCEEDS" = rev_72,  #dropped
         # "INTER-AGENCY RECEIPTS" = rev_75,  #dropped
      #  "TRANSFER IN FROM OUT FUNDS" = rev_76,  #other
         "ALL OTHER SOURCES" = rev_78,
         # "COOK COUNTY IGT" = rev_79, #dropped
         # "PRIOR YEAR REFUNDS" = rev_98 #dropped
  ) 

aggregate_rev_labels %>% mutate_all(., ~round(., digits = 0))

Table 11.2:

Aggregated Revenue Categories ($ Millions), with old labels

11.3.2 Expenditures

Create exp_970 for all local government transfers (exp_971 + exp_972 + exp_975 + exp_976).

Code

ff_exp <- exp_temp %>% 
  group_by(fy, group) %>% 
  summarize(sum_expenditures = sum(expenditure, na.rm=TRUE)/1000000 ) %>%
  pivot_wider(names_from = "group", values_from = "sum_expenditures", names_prefix = "exp_")%>%
  
    left_join(debt_keep_yearly) %>%
  mutate(exp_903 = debt_cost) %>%

  #  left_join(healthcare_costs_yearly) %>%

  # join state employee healthcare and subtract employee premiums
  # left_join(emp_premium, by = c("fy" = "year")) %>%
#  mutate(exp_904_new = (`healthcare_cost` - `employee_premiums_sum`)) %>% # state employee healthcare premiums
  
 # left_join(retirement_contributions) %>%
  #    mutate(exp_901_new = exp_901 - contributions/1000000) %>% #employee pension contributions


  # join local transfers and create exp_970
  left_join(transfers) %>%
  mutate(exp_970 = exp_971 + exp_972  + exp_975 + exp_976)

ff_exp<- ff_exp %>% 
  select(-c(debt_cost, exp_971:exp_976)) # drop unwanted columns

ff_exp # not labeled

Table 11.3:

Pivoted Expenditure Categories ($ Millions)

Code

# exp_temp %>%
#   group_by(year, group) %>%
#   summarize(sum_expenditure = round(sum(expenditure)/1000000)) %>%
#   arrange(year) %>%
#   pivot_wider(names_from = "group", values_from = "sum_expenditure")
# 

aggregate_exp_labeled <- exp_temp %>%
  group_by(year, group_name) %>%
  summarize(sum_expenditure = sum(expenditure)/1000000) %>%
  arrange(year) %>%
  pivot_wider(names_from = "group_name", values_from = "sum_expenditure")

aggregate_exp_labeled %>% mutate_all(., ~round(., digits = 0))

Table 11.4:

Final Expenditure Categories, with Fiscal Futures Grouped Expenditure Categories

12 Graphs and Tables

Create total revenues and total expenditures only:

after aggregating expenditures and revenues, pivoting wider, then I want to drop the columns that I no longer want and then pivot_longer(). After pivoting_longer() and creating rev_long and exp_long, expenditures and revenues are in the same format and can be combined together for the totals and gap each year.

Code

rev_long <- pivot_longer(ff_rev, rev_02:rev_78, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>% 
  rename(Year = fy) %>%
  mutate(Category_name = case_when(
    Category == "02" ~ "INDIVIDUAL INCOME TAXES" ,
    Category == "03" ~ "CORPORATE INCOME TAXES" ,
    Category == "06" ~ "SALES TAXES" ,
    Category == "09" ~ "MOTOR FUEL TAX" ,
    Category == "12" ~ "PUBLIC UTILITY TAXES" ,
    Category == "15" ~ "CIGARETTE TAXES" ,
    Category == "18" ~ "LIQUOR GALLONAGE TAXES" ,
    Category == "21" ~ "INHERITANCE TAX" ,
    Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES" ,
    Category == "27" ~ "CORP FRANCHISE TAXES & FEES" ,
    Category == "30" ~ "HORSE RACING TAXES & FEES",  # in Other
    Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" ,
    Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped
    Category == "33" ~  "LOTTERY RECEIPTS" ,
    Category == "35" ~  "OTHER TAXES" ,
    Category == "36" ~  "RECEIPTS FROM REVENUE PRODUCING", 
    Category == "39" ~  "LICENSES, FEES & REGISTRATIONS" ,
    Category == "42" ~  "MOTOR VEHICLE AND OPERATORS" ,
    Category == "45" ~  "STUDENT FEES-UNIVERSITIES",   # dropped
    Category == "48" ~  "RIVERBOAT WAGERING TAXES" ,
    Category == "51" ~  "RETIREMENT CONTRIBUTIONS" , # dropped
    Category == "54" ~ "GIFTS AND BEQUESTS", 
    Category == "57" ~  "FEDERAL OTHER" ,
    Category == "58" ~  "FEDERAL MEDICAID", 
    Category == "59" ~  "FEDERAL TRANSPORTATION" ,
    Category == "60" ~  "OTHER GRANTS AND CONTRACTS", #other
    Category == "63" ~  "INVESTMENT INCOME", # other
    Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped
    Category == "72" ~ "BOND ISSUE PROCEEDS",  #dropped
    Category == "75" ~  "INTER-AGENCY RECEIPTS ",  #dropped
    Category == "76" ~  "TRANSFER IN FROM OUT FUNDS",  #other
    Category == "78" ~  "ALL OTHER SOURCES" ,
    Category == "79" ~   "COOK COUNTY IGT", #dropped
    Category == "98" ~  "PRIOR YEAR REFUNDS", #dropped
                 T ~ "Check Me!"

  ) )%>% 
  mutate(Category_name = str_to_title(Category_name))


exp_long <- pivot_longer(ff_exp, exp_402:exp_970 , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>% 
  rename(Year = fy ) %>% 
  mutate(Category_name = 
           case_when(
             Category == "402" ~ "AGING" ,
             Category == "406" ~ "AGRICULTURE", 
             Category == "416" ~ "CENTRAL MANAGEMENT",
             Category == "418" ~ "CHILDREN AND FAMILY SERVICES", 
             Category == "420" ~ "COMMERCE AND ECONOMIC OPPORTUNITY",
             Category == "422" ~ "NATURAL RESOURCES" ,
             Category == "426" ~ "CORRECTIONS",
             Category == "427" ~ "EMPLOYMENT SECURITY" ,
             Category == "444" ~ "HUMAN SERVICES" ,
             Category == "448" ~ "Innovation and Technology", # AWM added fy2022
             Category == "478" ~ "FAMILY SERVICES net Medicaid", 
             Category == "482" ~ "PUBLIC HEALTH", 
             Category == "492" ~ "REVENUE", 
             Category == "494" ~ "TRANSPORTATION" ,
             Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
             Category == "557" ~ "IL STATE TOLL HIGHWAY AUTH" ,
             Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", 
             Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
             Category == "900" ~ "NOT IN FRAME",
             Category == "901" ~ "STATE PENSION CONTRIBUTION",
             Category == "903" ~ "DEBT SERVICE",
             Category == "904" ~ "State Employee Healthcare",
             Category == "910" ~ "LEGISLATIVE"  ,
             Category == "920" ~ "JUDICIAL" ,
             Category == "930" ~ "ELECTED OFFICERS" , 
             Category == "940" ~ "OTHER HEALTH-RELATED", 
             Category == "941" ~ "PUBLIC SAFETY" ,
             Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
             Category == "943" ~ "CENTRAL SERVICES",
             Category == "944" ~ "BUS & PROFESSION REGULATION" ,
             Category == "945" ~ "MEDICAID" ,
             Category == "946" ~ "CAPITAL IMPROVEMENT" , 
             Category == "948" ~ "OTHER DEPARTMENTS" ,
             Category == "949" ~ "OTHER BOARDS & COMMISSIONS" ,
             Category == "959" ~ "K-12 EDUCATION" ,
             Category == "960" ~ "UNIVERSITY EDUCATION",
             Category == "970" ~ "Local Govt Transfers",
             T ~ "CHECK ME!")
           ) %>% 
  mutate(Category_name = str_to_title(Category_name))

#write_csv(exp_long, "expenditures_recoded_long_FY23.csv")
#write_csv(rev_long, "revenue_recoded_long_FY23.csv")

aggregated_totals_long <- rbind(rev_long, exp_long)

aggregated_totals_long %>% mutate(`Dollars (Millions)` = round(Dollars, digits = 0)) %>% select(-Dollars) %>%
  select(Year, Category_name, `Dollars (Millions)`, type, Category)

Table 12.1:

Long Version of Data that has Revenue and Expenditures in One Dataframe

Code

year_totals <- aggregated_totals_long %>% 
  group_by(type, Year) %>% 
  summarize(Dollars = sum(Dollars, na.rm = TRUE)) %>% 
  pivot_wider(names_from = "type", values_from = Dollars) %>% 

  rename(
         Expenditures = exp,
         Revenue = rev) %>%  
  mutate(`Fiscal Gap` = round(Revenue - Expenditures))
# %>%  arrange(desc(Year))
# creates variable for the Gap each year

year_totals  %>%  
  mutate_all(., ~round(., digits = 0)) %>%
  kbl(caption = "Fiscal Gap for each Fiscal Year ($ Millions)") %>% 
  kable_styling(bootstrap_options = c("striped"))  %>%
  kable_classic() %>%   
  add_footnote(c("Values include State CURE dollars (SLFRF)"))

Table 12.2: ?(caption)

(a) Fiscal Gap for each Fiscal Year ($ Millions)
Year	Expenditures	Revenue	Fiscal Gap
1998	31218	31265	46
1999	33805	33030	-775
2000	37283	35846	-1437
2001	40300	37148	-3153
2002	42014	36826	-5188
2003	42567	36806	-5761
2004	52980	40856	-12124
2005	45331	42866	-2465
2006	48028	44701	-3328
2007	51099	48033	-3065
2008	54139	50213	-3925
2009	56721	49859	-6862
2010	59248	49839	-9409
2011	60404	54732	-5672
2012	59831	56248	-3583
2013	63261	60804	-2457
2014	66942	62772	-4169
2015	69921	64114	-5807
2016	63909	61986	-1924
2017	71705	61349	-10356
2018	74943	70465	-4477
2019	74384	72153	-2231
2020	81574	78142	-3433
2021	92807	91806	-1001
2022	99786	113029	13243
2023	110575	108086	-2489
^a Values include State CURE dollars (SLFRF)

Graphs made from aggregated_totals_long dataframe.

Expenditure and revenue amounts in billions of dollars:

Expenditure and revenues when focusing on largest categories and combining others into “All Other Expenditures(Revenues)”:

Code

exp_long %>%
  filter( Year == 2023) %>%
  mutate(rank = rank(Dollars),
        Category_name = ifelse(rank > 13, Category_name, 'All Other Expenditures')) %>%
 # select(-c(Year, Dollars, rank)) %>%
  arrange(desc(Dollars)) %>%
  ggplot() + 
  geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`/1000), fill = "rosybrown2")+ 
  coord_flip() +
      theme_classic() +
    labs(title = "Expenditures for FY2023") +
    xlab("") +
  ylab("Billions of Dollars")

rev_long %>%
  filter( Year == 2023) %>%
  mutate(rank = rank(Dollars),
        Category_name = ifelse(rank > 10, Category_name, 'All Other Sources')) %>%
  arrange(desc(Dollars)) %>%
  ggplot() + 
  geom_col(aes(x = fct_reorder(Category_name, `Dollars`/1000), y = `Dollars`/1000), fill = "dark gray")+ 
  coord_flip() +
      theme_classic() +
    labs(title = "Revenues for FY2023") +
    xlab("") +
  ylab("Billions of Dollars")

Figure 12.3: Largest Expenditures for FY2023

Figure 12.4: Largest Revenue Sources for FY2023

Changes in Categories - 2022 to 2023

12.0.1 Top 3 Revenues

Code

annotation <- data.frame(
  x = c(2013, 2018, 2013),
  y = c(16, 10, 5),  
  label = c("Individual Income Tax", "Sales Tax", "Corporate Income Tax")
)

top3 <- rev_long  %>% 
  filter(Category == "02" | Category == "03" | Category == "06")

top3 <- ggplot(data = top3, aes(x=Year, y=Dollars/1000))+
      geom_recessions(text = FALSE)+
  geom_line(aes(x=Year, y=Dollars/1000, color = Category_name)) + 
  geom_text(data = annotation, aes(x=x, y=y, label=label)) +
  theme_classic() +
  scale_x_continuous(expand = c(0,0)) +
  scale_y_continuous(labels = comma) +
  scale_linetype_manual(values = c("dotted", "dashed", "solid")) +
  theme(legend.position = "none") +
  labs(title = "Top 3 Own Source Revenues", 
       subtitle = "Individual Income Taxes, Sales Tax, and Corporate income taxes",
       y = "Billions of Nominal Dollars") 
  
top3

Figure 12.6: Top 3 Revenue Sources (Own-Source Revenues only)

Sales Tax - online retailers

Online Retailer Warning

Not edited or double checked. Randomly looked into online retailers recently and didn’t finish thoughts on it. Just general notes pulled together while looking into online sales tax.

Law was passed in 2018 that required out of state retailers to pay the 6.25% state sales tax. The Rebuild Illinois law expanded the law to require remote retailers to charge all state and local retailers occupation taxes beginning in July 1, 2020. Before Jan. 1 2021, only state sales taxes were required to be collected (related to South Dakota v Wayfair court decision). Now required to pay state and local tax based on where product is delivered.

“On June 28, 2019, Public Act 101-0031, the”Leveling the Playing Field for Illinois Retail Act,” was signed into Illinois law and on December 13, 2019 an amendment to the Act was signed into law in Public Act 101-0604. In an effort to create more equity between remote sellers and local brick-and-mortar retailers, the new law requires remote sellers without a physical presence in the state and marketplace facilitators (e.g., Amazon and Walmart) to collect both state and local sales taxes effective January 1, 2021.” CivicFed.org

Requires remote sellers and marketplace facilitators to collect and remit the state and locally-imposed Retailers’ Occupation Tax (ROT) for the jurisdictions where the product is delivered (destination sourcing) rather than collecting and remitting solely the state use tax.

Illinois’ State sales tax rate is 6.25%, of which 5.0% of the sales tax revenue goes to the State, 1.0% goes to all municipalities, including Chicago, and the remaining 0.25% goes to the counties. However, Cook County’s 0.25% share of the State sales tax is distributed to the Regional Transportation Authority.

“The amended”Leveling the Playing Field for Illinois Retail Act” was passed by the General Assembly on November 14, 2019, to require both Remote Retailers and Marketplace Facilitators to collect and remit the state and locally-imposed Retailers’ Occupation Tax (ROT, aka sales tax) for the jurisdictions where the product is delivered (its destination) starting January 1, 2021.”- Illinois Municipal League

Marketplace Facilitators, like Amazon, were required to collect Use Tax on sales starting January 1, 2020
Other sellers required to collect state and local sales tax on sales on January 2021.
There is a state tax rate of 6.25% and Illinois municipalities may impose an additional local sales tax called the Retailer’s Occupation Tax.
- For remote sellers, the state tax rate is referred to as “use tax” and for intrastate sellers, “ROT” simply means sales tax.
- The ROT is measured upon the seller’s gross receipts and the seller is statutorily required to collect the use tax from their customers.
source 0482 is State ROT-2.2%

ILGA info - leveling the playing field went into effect on July 1 2020 which is the beginning of FY21

Code

## State Retailers Occupation Tax. 
rev_temp %>% filter(source == "0481") %>%
  group_by(fy, source_name_AWM) %>% summarize(revenue=sum(receipts))

Code

rev_temp %>% 
  filter(source == "0481") %>%
  group_by(fy, source_name_AWM, fund_name_ab) %>% 
  summarize(revenue=sum(receipts))%>%
  arrange(-fy, -revenue)%>%
  pivot_wider(names_from = "fy", values_from="revenue")

Code

rev_temp %>% 
  filter(source == "0481") %>%
  #group_by(fy, source_name_AWM, fund_name_ab) %>% 
#  summarize(revenue=sum(receipts))  %>% 
  ggplot(aes(x=fy, y=receipts))+
  geom_recessions()+
  geom_line(aes(color=fund_name_ab))+
  geom_vline(xintercept = 2018)+
  geom_vline(xintercept = 2021)+
  theme_classic()+
  scale_x_continuous(expand = c(0,0))

Code

  labs(title="State Retailers' Occupation Tax, Source 0481",
       caption = "Leveling the Playing Field went into effect for Amazon on January 1, 2020(mid-FY21) 
       and for other remote retailers starting January 1, 2021 (mid-FY22)")

$title
[1] "State Retailers' Occupation Tax, Source 0481"

$caption
[1] "Leveling the Playing Field went into effect for Amazon on January 1, 2020(mid-FY21) \n       and for other remote retailers starting January 1, 2021 (mid-FY22)"

attr(,"class")
[1] "labels"

Code

### Remote Occupation Tax
# STATE ROT-2.2%
rev_temp %>% 
  filter(source == "0482") %>%
  group_by(fy, source_name_AWM) %>% 
  summarize(revenue=sum(receipts))

Code

rev_temp %>% 
  filter(source == "0482") %>%
  group_by(fy, source_name_AWM, fund_name_ab) %>% 
  summarize(revenue=sum(receipts))%>%
  arrange(-fy, -revenue)%>%
  pivot_wider(names_from = "fy", values_from="revenue")

Code

rev_temp %>% 
  filter(source == "0482") %>%
  #group_by(source_name_AWM) %>% 
  #summarize(revenue=sum(receipts))  %>% 
  ggplot(aes(x=fy, y=receipts))+
  geom_line(aes(color=fund_name_ab))+
  geom_recessions()+
  geom_vline(xintercept = 2018)+

  geom_vline(xintercept = 2020)+
  theme_classic() +
  scale_x_continuous(expand = c(0,0)) +
  labs(title="State Retailers' Occupation Tax",
       subtitle = "Large increases due to Leveling the Playing Field Act & Online shopping during pandemic")

Code

rev_temp %>% 
  filter(source == "0482") %>%
  group_by(fy, source_name_AWM, fund_name_ab) %>% 
  summarize(revenue=sum(receipts))  %>% 
  ggplot()+
  geom_line(aes(x=fy, y=revenue, color=fund_name_ab))+
  geom_vline(xintercept = 2018) +
  geom_vline(xintercept = 2021) +
  #geom_recessions(aes(x=fy, y=receipts)+
  theme_classic()+
  scale_x_continuous(expand = c(0,0)) +
  labs(title="State ROT - 2.2%",
       subtitle = "Large increases due to Leveling the Playing Field Act & Online shopping during pandemic??",
       caption = "State tax began being collected for remote retailers based on 
       destination beginning in Leveling the Playing Field went into effect for Amazon on January 1, 2020(mid-FY21) and for other 
       remote retailers starting January 1, 2021 (mid-FY22)")

Code

rev_temp %>% 
  filter(source == "0482" | source == "0481") %>%
  group_by(fy, source_name_AWM) %>% 
  summarize(revenue=sum(receipts))  %>% 
  ggplot()+
  geom_line(aes(x=fy, y=revenue, color=source_name_AWM))+
  geom_vline(xintercept = 2018)+
  geom_vline(xintercept = 2021)+
  #geom_recessions(aes(x=fy, y=receipts)+
  theme_classic()+
  scale_x_continuous(expand = c(0,0)) + 

  labs(title="State ROT - 2.2% & ",
       subtitle = "Large increases due to Leveling the Playing Field Act & Online shopping during pandemic??",
       caption = "Leveling the Playing Field went into effect for Amazon on January 1, 2020(mid-FY21) 
       and for other remote retailers starting January 1, 2021 (mid-FY22)")

As of Feb. 6 2023, Source 481 Retailers Occupation Tax has collected $9.3 billion already. FY22 had $14.7 million. Around half goes to the General Revenue Fund.

12.0.2 Own Source and Fed Transfers

Code

ownsource_rev <- rev_long %>%
  filter(!Category %in% c("57", "58", "59")) %>%
  group_by(Year) %>% 
  summarize(Dollars = sum(Dollars))

# ownsource_rev %>% 
#   ggplot()+geom_line(aes(x=Year, y=Dollars)) + 
#   labs(title = "Own Source Revenues", subtitle = "Total own source revenue", y = "Millions of Dollars")

fed_rev <- ff_rev %>% select(fy, rev_57, rev_58, rev_59) %>%
  mutate(fed_total = rev_57+rev_58+rev_59)


annotation <- data.frame(
  x = c(2014, 2015),
  y = c(50, 25),  
  label = c("Own Source Revenue", "Federal Revenue")
)


ownsource_graph <- ggplot(ownsource_rev, aes(x=Year, y=Dollars/1000)) + 
  geom_recessions( text = FALSE)+
  geom_line(data = ownsource_rev, aes(x=Year, y=Dollars/1000), color = "Red") + 
  geom_line(data = fed_rev, aes(x=fy, y=fed_total/1000), color = "Black") + 
    geom_text(data = annotation, aes(x=x, y=y, label=label))+
    scale_y_continuous(labels = comma)+
  scale_x_continuous(expand = c(0,0)) + 
  theme(legend.position = "none")+

  theme_classic()+
  labs(title = "Own Source Revenue and Federal Revenue", 
  y = "Billions of Nominal Dollars")
ownsource_graph

ggsave(plot = ownsource_graph, file = "Figure4.eps")

Figure 12.7: Comparison of Own Source and Federal Revenue. Historicaly, federal revenue tends to increase when state revenue decreases from some sort of economic shock (e.g. Housing Bubble in 2008).

13 Change from Previous Year

Each year, you will need to update the CAGR formulas! Change the filter() year.

calc_cagr is a function created for calculating the CAGRs for different spans of time.

Code

exp_totals <- ff_exp %>% 
  rowwise() %>% 
  mutate(exp_TOTALS = sum(across(exp_402:exp_970)))

rev_totals <- ff_rev %>%    
  rowwise() %>% 
  mutate(rev_TOTALS = sum(across(rev_02:rev_78)))



rev_long <- pivot_longer(rev_totals, rev_02:rev_TOTALS, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>% 
  rename(Year = fy) %>%
  mutate(Category_name = case_when(
    Category == "02" ~ "INDIVIDUAL INCOME TAXES" ,
    Category == "03" ~ "CORPORATE INCOME TAXES" ,
    Category == "06" ~ "SALES TAXES" ,
    Category == "09" ~ "MOTOR FUEL TAX" ,
    Category == "12" ~ "PUBLIC UTILITY TAXES" ,
    Category == "15" ~ "CIGARETTE TAXES" ,
    Category == "18" ~ "LIQUOR GALLONAGE TAXES" ,
    Category == "21" ~ "INHERITANCE TAX" ,
    Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES" ,
    Category == "27" ~ "CORP FRANCHISE TAXES & FEES" ,
    Category == "30" ~ "HORSE RACING TAXES & FEES",  # in Other
    Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" ,
    Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped
    Category == "33" ~  "LOTTERY RECEIPTS" ,
    Category == "35" ~  "OTHER TAXES" ,
    Category == "36" ~  "RECEIPTS FROM REVENUE PRODUCING", 
    Category == "39" ~  "LICENSES, FEES & REGISTRATIONS" ,
    Category == "42" ~  "MOTOR VEHICLE AND OPERATORS" ,
    Category == "45" ~  "STUDENT FEES-UNIVERSITIES",   # dropped
    Category == "48" ~  "RIVERBOAT WAGERING TAXES" ,
    Category == "51" ~  "RETIREMENT CONTRIBUTIONS" , # dropped
    Category == "54" ~ "GIFTS AND BEQUESTS", 
    Category == "57" ~  "FEDERAL OTHER" ,
    Category == "58" ~  "FEDERAL MEDICAID", 
    Category == "59" ~  "FEDERAL TRANSPORTATION" ,
    Category == "60" ~  "OTHER GRANTS AND CONTRACTS", #other
    Category == "63" ~  "INVESTMENT INCOME", # other
    Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped
    Category == "72" ~ "BOND ISSUE PROCEEDS",  #dropped
    Category == "75" ~  "INTER-AGENCY RECEIPTS ",  #dropped
    Category == "76" ~  "TRANSFER IN FROM OUT FUNDS",  #other
    Category == "78" ~  "ALL OTHER SOURCES" ,
    Category == "79" ~   "COOK COUNTY IGT", #dropped
    Category == "98" ~  "PRIOR YEAR REFUNDS", #dropped
    Category == "TOTALS" ~ "Total",
    T ~ "CHECK ME"
    
  ) ) %>% 
  select(-type, -Category) %>%  # drop extra columns type and Category number
  group_by(Year, Category_name) %>%
  summarise(Dollars= round(sum(Dollars),digits=2)) %>% 
  mutate(Category_name = str_to_title(Category_name))

# creates wide version of table where each revenue source is a column
revenue_wide2 <- rev_long %>% pivot_wider(names_from = Category_name, 
                                          values_from = Dollars) %>%
  #  relocate("Other Revenue Sources **", .after = last_col()) %>%
  relocate("Total", .after =  last_col())

exp_long <- pivot_longer(exp_totals, exp_402:exp_TOTALS , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>%
  rename(Year = fy ) %>% 
  mutate(Category_name = 
           case_when(
             Category == "402" ~ "AGING" ,
             Category == "406" ~ "AGRICULTURE", 
             Category == "416" ~ "Central Management",
             Category == "418" ~ "CHILDREN AND FAMILY SERVICES", 
             Category == "420" ~ "Community Development",
             Category == "422" ~ "NATURAL RESOURCES" ,
             Category == "426" ~ "CORRECTIONS",
             Category == "427" ~ "EMPLOYMENT SECURITY" ,
             Category == "444" ~ "Human Services" ,
             Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", 
             Category == "482" ~ "PUBLIC HEALTH", 
             Category == "492" ~ "REVENUE", 
             Category == "494" ~ "Transportation" ,
             Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
             Category == "557" ~ "Tollway" ,
             Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", 
             Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
             Category == "900" ~ "NOT IN FRAME",
             Category == "901" ~ "State Pension Contribution",
             Category == "903" ~ "Debt Service",
             Category == "904" ~ "State Employee Healthcare",
             Category == "910" ~ "LEGISLATIVE"  ,
             Category == "920" ~ "JUDICIAL" ,
             Category == "930" ~ "ELECTED OFFICERS" , 
             Category == "940" ~ "OTHER HEALTH-RELATED", 
             Category == "941" ~ "Public Safety" ,
             Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
             Category == "943" ~ "CENTRAL SERVICES",
             Category == "944" ~ "BUS & PROFESSION REGULATION" ,
             Category == "945" ~ "Medicaid" ,
             Category == "946" ~ "Capital Improvement" , 
             Category == "948" ~ "OTHER DEPARTMENTS" ,
             Category == "949" ~ "OTHER BOARDS & COMMISSIONS" ,
             Category == "959" ~ "K-12 Education" ,
             Category == "960" ~ "UNIVERSITY EDUCATION",
             Category == "970" ~ "Local Govt Revenue Sharing",
             Category == "TOTALS" ~ "Total",
             T ~ "CHECK ME" 
             # T ~ "All Other Expenditures **")
           )) %>% 
  select(-type, -Category) %>% 
  group_by(Year, Category_name) %>% 
  summarise(Dollars= round(sum(Dollars),digits=2)) %>% 
  mutate(Category_name = str_to_title(Category_name))

expenditure_wide2 <- exp_long%>% 
  pivot_wider(names_from = Category_name, 
              values_from = Dollars) %>%
  relocate("Total", .after =  last_col())


# function for calculating the CAGR
calc_cagr <- function(df, n) {
  df <- exp_long %>%
    #select(-type) %>%
    arrange(Category_name, Year) %>%
    group_by(Category_name) %>%
    mutate(cagr = ((`Dollars` / lag(`Dollars`, n)) ^ (1 / n)) - 1)
  
  return(df)
}

Things to do when updating the code:

Each year, you need to increase the cagr value by 1. The value should be the (current year - 1998). For FY23, this is 2023-1998 = 25. So all cagr values that were 24 will be changed to 25.

Code

# This works for one variable at a time
cagr_25 <- calc_cagr(exp_long, 25) %>% 
  # group_by(Category) %>%
  summarize(cagr_25 = round(sum(cagr*100, na.rm = TRUE), 2))

# doesn't need to be changed since it is just pre-covid 
cagr_precovid <- exp_long %>%
  filter(Year <= 2019) %>%
  calc_cagr(21) %>% 
  summarize(cagr_21 = round(sum(cagr*100, na.rm = TRUE), 2))


# Update year in the filter() and summarize() commands to current year.
cagr_10 <- calc_cagr(exp_long, 10) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_10 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_5 <- calc_cagr(exp_long, 5) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_5 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_3 <- calc_cagr(exp_long, 3) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_3 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_2 <- calc_cagr(exp_long, 2) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_2 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_1 <- calc_cagr(exp_long, 1) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_1 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

# update variables so cagr_24 becomes cagr_25
CAGR_expenditures_summary_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_25 ) %>% 
  select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>% 
  rename("Expenditure Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10,"25 Year CAGR" = cagr_25 )

move_to_last <- function(df, n) df[c(setdiff(seq_len(nrow(df)), n), n), ]

CAGR_expenditures_summary_tot <- move_to_last(CAGR_expenditures_summary_tot, 29
                                          ) 

#CAGR_expenditures_summary_tot <-   select(CAGR_expenditures_summary_tot, -1) 

CAGR_expenditures_summary_tot %>%   
  kbl(caption = "CAGR Calculations for All Expenditure Categories" , row.names=FALSE) %>% 
  kable_classic() %>%
  row_spec(31, bold = T, color = "black", background = "gray")

Table 13.1: Expenditure Category CAGRs with Total CAGR (Ordered Alphabetically)
Expenditure Category	1 Year CAGR	2 Year CAGR	3 Year CAGR	5 Year CAGR	10 Year CAGR	25 Year CAGR
Aging	17.50	9.85	9.02	7.66	2.10	7.72
Agriculture	-0.61	14.81	7.18	0.98	2.43	0.81
Bus & Profession Regulation	6.40	7.19	5.88	3.81	-1.74	1.62
Capital Improvement	52.57	19.39	28.00	23.57	2.05	3.80
Central Management	17.75	7.01	4.64	6.16	4.02	4.76
Children And Family Services	25.99	10.03	8.40	7.78	3.33	0.78
Community Development	7.71	-5.46	34.18	24.63	4.34	4.79
Corrections	13.46	2.80	3.47	-2.65	3.13	2.21
Debt Service	-2.89	-1.86	0.07	-0.20	0.80	5.73
Elected Officers	7.98	5.72	6.16	4.82	4.52	3.88
Employment Security	-1.39	-9.04	4.63	2.75	0.17	1.62
Environmental Protect Agency	-5.68	-4.43	-5.01	-7.42	1.38	2.78
Healthcare & Fam Ser Net Of Medicaid	13.37	4.29	6.79	0.77	0.93	5.46
Human Services	21.34	16.10	13.77	10.19	4.64	3.29
Judicial	20.70	8.56	8.59	7.29	5.65	3.37
K-12 Education	9.86	9.86	9.15	7.04	5.35	4.35
Legislative	66.45	41.14	27.74	18.72	5.76	5.19
Local Govt Revenue Sharing	5.36	23.23	19.08	12.02	6.46	4.68
Medicaid	13.10	11.05	13.29	11.76	8.17	7.43
Natural Resources	10.42	5.22	4.99	4.85	2.81	1.95
Other Boards & Commissions	33.54	14.97	15.85	8.46	1.53	5.11
Other Departments	16.22	8.65	8.38	8.06	6.03	9.36
Public Health	-8.38	-9.79	11.07	15.13	7.76	6.44
Public Safety	1.18	-6.87	5.37	14.12	7.58	5.69
Revenue	8.02	8.66	20.73	30.50	17.31	6.39
State Employee Healthcare	-0.09	2.08	0.23	-12.49	3.28	5.82
State Pension Contribution	5.06	10.12	8.85	9.54	9.06	10.52
Tollway	-9.89	-1.78	-0.41	5.23	7.14	6.78
Transportation	16.29	-2.95	7.20	6.92	1.59	3.80
University Education	12.52	7.50	5.01	4.60	0.98	0.81
Total	10.81	9.15	10.67	8.09	5.74	5.19

Code

# revenue version function:
calc_cagr <- function(df, n) {
  df <- rev_long %>%
    arrange(Category_name, Year) %>%
    group_by(Category_name) %>%
    mutate(cagr = ((Dollars / lag(Dollars, n)) ^ (1 / n)) - 1)

  return(df)
}

# This works for one variable at a time
cagr_25 <- calc_cagr(rev_long, 25) %>% 
     # group_by(Category) %>%
  summarize(cagr_25 = round(sum(cagr*100, na.rm = TRUE), 2))

cagr_10 <- calc_cagr(rev_long, 10) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_10 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_5 <- calc_cagr(rev_long, 5) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_5 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_3 <- calc_cagr(rev_long, 3) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_3 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_2 <- calc_cagr(rev_long, 2) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_2 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

 cagr_1 <- calc_cagr(rev_long, 1) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_1 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

CAGR_revenue_summary_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_25) %>%   
  select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>% 
  rename("Revenue Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10,"25 Year CAGR" = cagr_25 )

CAGR_revenue_summary_tot <- move_to_last(CAGR_revenue_summary_tot,1)
CAGR_revenue_summary_tot <- move_to_last(CAGR_revenue_summary_tot,22)

CAGR_revenue_summary_tot %>% 
  kbl(caption = "CAGR Calculations for All Revenue Sources (Ordered Alphabetical)", row.names = FALSE) %>% 
   kable_classic() %>%
    row_spec(23, bold = T, color = "black", background = "gray")

Table 13.2: Revenue Category CAGRs with Total CAGR (Ordered Alphabetically)
Revenue Category	1 Year CAGR	2 Year CAGR	3 Year CAGR	5 Year CAGR	10 Year CAGR	25 Year CAGR
Cigarette Taxes	-6.72	-7.49	-2.64	0.53	-0.87	2.12
Corp Franchise Taxes & Fees	4.40	-15.99	2.27	1.66	0.97	2.62
Corporate Income Taxes	4.31	35.75	46.02	28.69	9.92	7.57
Federal Medicaid	6.09	7.28	13.44	8.44	9.32	7.47
Federal Other	-43.87	8.68	3.88	13.39	5.67	4.35
Federal Transportation	15.30	-5.74	5.83	5.82	2.17	3.78
Gifts And Bequests	13.17	18.34	31.73	11.70	10.40	11.50
Individual Income Taxes	-7.27	2.18	7.87	5.60	4.09	5.13
Inheritance Tax	-17.90	5.66	21.71	7.21	5.71	2.78
Insurance Taxes&Fees&Licenses	8.76	2.49	11.41	3.34	3.82	6.64
Licenses, Fees & Registrations	9.25	2.24	13.23	11.07	6.18	7.94
Liquor Gallonage Taxes	-1.16	0.67	1.47	1.32	1.23	7.09
Lottery Receipts	11.88	2.46	10.37	4.32	1.23	2.53
Medical Provider Assessments	9.50	3.60	5.58	13.15	9.46	8.41
Motor Fuel Tax	1.51	3.79	3.40	13.47	7.38	2.73
Motor Vehicle And Operators	-0.01	-2.84	3.08	1.49	0.89	3.08
Other Taxes	12.99	36.08	25.80	14.85	16.71	8.07
Public Utility Taxes	2.23	2.66	0.44	0.16	-0.43	0.76
Receipts From Revenue Producing	8.47	5.70	5.99	0.89	2.69	5.21
Riverboat Wagering Taxes	9.37	40.61	2.32	-4.22	-3.82	2.05
Sales Taxes	4.81	8.00	9.70	6.61	4.68	3.29
All Other Sources	50.65	44.03	24.31	14.21	10.57	6.08
Total	-4.37	8.50	11.42	8.93	5.92	5.09

Update all years in mutate() commands so that they all go up by 1:

Code

revenue_change2 <- rev_long %>%
  #select(-c(Category)) %>%
  filter(Year > 2021) %>%
  pivot_wider(names_from = Year , values_from = Dollars,   names_prefix = "Dollars_") %>%
  mutate(
    "FY 2023 ($ billions)" = round(Dollars_2023/1000, digits = 1),
    "FY 2022 ($ billions)" = round(Dollars_2022/1000, digits = 1),

#    "Change from 2022 to 2023" = round(Dollars_2022 - Dollars_2021, digits = 2),
         "1-Year Change" = round(((Dollars_2023 -Dollars_2022)/Dollars_2022*100), digits = 2)) %>%
  left_join(CAGR_revenue_summary_tot, by = c("Category_name" = "Revenue Category")) %>% 
    arrange(-`FY 2023 ($ billions)`)%>%
#  filter(Category_ame != "NA") %>%
  #select(-c(Dollars_2021, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
  rename( "25-Year CAGR" = `25 Year CAGR`, 
          "Revenue Category" = Category_name ) %>%
  select(-c(Dollars_2022, Dollars_2023, `1 Year CAGR`:`10 Year CAGR`)) 


revenue_change2 <- move_to_last(revenue_change2,8)
revenue_change2 <- move_to_last(revenue_change2,1)

revenue_change2 %>%   
  filter(!is.na(`Revenue Category`)) %>%

  kbl(caption = "Table 1. Yearly Change in Revenue", row.names = FALSE) %>% 
   kable_classic() %>%
    row_spec(23, bold = T, color = "black", background = "gray")

Table 1. Yearly Change in Revenue
Revenue Category	FY 2023 ($ billions)	FY 2022 ($ billions)	1-Year Change	25-Year CAGR
Individual Income Taxes	22.1	23.8	-7.27	5.13
Federal Medicaid	20.2	19.0	6.09	7.47
Sales Taxes	16.2	15.4	4.81	3.29
Federal Other	10.9	19.4	-43.87	4.35
Corporate Income Taxes	10.1	9.7	4.31	7.57
All Other Sources	4.1	2.7	50.65	6.08
Receipts From Revenue Producing	2.6	2.4	8.47	5.21
Motor Fuel Tax	2.5	2.5	1.51	2.73
Federal Transportation	2.1	1.8	15.30	3.78
Gifts And Bequests	2.1	1.9	13.17	11.50
Licenses, Fees & Registrations	2.1	1.9	9.25	7.94
Lottery Receipts	1.6	1.4	11.88	2.53
Motor Vehicle And Operators	1.6	1.6	-0.01	3.08
Other Taxes	1.6	1.4	12.99	8.07
Public Utility Taxes	1.4	1.4	2.23	0.76
Cigarette Taxes	0.8	0.8	-6.72	2.12
Insurance Taxes&Fees&Licenses	0.7	0.6	8.76	6.64
Inheritance Tax	0.5	0.6	-17.90	2.78
Liquor Gallonage Taxes	0.3	0.3	-1.16	7.09
Riverboat Wagering Taxes	0.3	0.3	9.37	2.05
Corp Franchise Taxes & Fees	0.2	0.2	4.40	2.62
Medical Provider Assessments	4.1	3.7	9.50	8.41
Total	108.1	113.0	-4.37	5.09

Code

expenditure_change2 <- exp_long %>%
  #select(-c(type,Category)) %>%
  filter(Year > 2021) %>%
  pivot_wider(names_from = Year , values_from = Dollars,   names_prefix = "Dollars_") %>%
  mutate(
    "FY 2023 ($ billions)" = round(Dollars_2023/1000, digits = 1),
    "FY 2022 ($ billions)" = round(Dollars_2022/1000, digits = 1),

  #  "Change from 2021 to 2022" = Dollars_2022 - Dollars_2021,
         "1-Year Change" = round((Dollars_2023 -Dollars_2022)/Dollars_2022*100, digits = 2) )%>%
  left_join(CAGR_expenditures_summary_tot, by = c("Category_name" = "Expenditure Category")) %>% 
  arrange(-`FY 2023 ($ billions)`)%>%
  select(-c(Dollars_2023, Dollars_2022, `1 Year CAGR`:`10 Year CAGR`)) %>%

  rename( "25-Year CAGR" = `25 Year CAGR`, 
          "Expenditure Category" = Category_name )

expenditure_change2 <- move_to_last(expenditure_change2, 1)

expenditure_change2 %>% 

 filter(!is.na(`Expenditure Category`)) %>%

  kbl(caption = "Table 2. Yearly Change in Expenditures - All FF Categories, Ordered from Largest to Smallest Expenditure Amount", row.names = FALSE) %>% 
  kable_classic() %>%
    row_spec(31, bold = T, color = "black", background = "gray")

Table 2. Yearly Change in Expenditures - All FF Categories, Ordered from Largest to Smallest Expenditure Amount
Expenditure Category	FY 2023 ($ billions)	FY 2022 ($ billions)	1-Year Change	25-Year CAGR
Medicaid	32.4	28.7	13.10	7.43
K-12 Education	14.7	13.4	9.86	4.35
Local Govt Revenue Sharing	10.9	10.3	5.36	4.68
Human Services	8.8	7.3	21.34	3.29
State Pension Contribution	6.8	6.5	5.06	10.52
Other Departments	5.7	4.9	16.22	9.36
Transportation	5.0	4.3	16.29	3.80
State Employee Healthcare	3.0	3.0	-0.09	5.82
University Education	2.5	2.3	12.52	0.81
Debt Service	2.0	2.0	-2.89	5.73
Revenue	2.0	1.8	8.02	6.39
Tollway	1.9	2.1	-9.89	6.78
Corrections	1.7	1.5	13.46	2.21
Public Safety	1.7	1.7	1.18	5.69
Children And Family Services	1.6	1.3	25.99	0.78
Community Development	1.5	1.4	7.71	4.79
Aging	1.4	1.2	17.50	7.72
Central Management	1.4	1.2	17.75	4.76
Elected Officers	1.1	1.0	7.98	3.88
Public Health	0.8	0.8	-8.38	6.44
Capital Improvement	0.7	0.4	52.57	3.80
Environmental Protect Agency	0.6	0.7	-5.68	2.78
Judicial	0.6	0.5	20.70	3.37
Healthcare & Fam Ser Net Of Medicaid	0.4	0.4	13.37	5.46
Employment Security	0.3	0.3	-1.39	1.62
Natural Resources	0.3	0.3	10.42	1.95
Other Boards & Commissions	0.3	0.2	33.54	5.11
Bus & Profession Regulation	0.2	0.2	6.40	1.62
Legislative	0.2	0.1	66.45	5.19
Agriculture	0.1	0.1	-0.61	0.81
Total	110.6	99.8	10.81	5.19

13.1 Summary Tables - Largest Categories

The 10 largest revenue sources and 15 largest expenditure sources remain separate categories and all other smaller sources/expenditures are combined into “All Other Revenues (Expenditures)”. These condensed tables are typically used in the Fiscal Futures articles. They were manually created in past years but this hopefully automates the process a bit until final formatting stages.

take ff_rev and ff_exp data frames, which were in wide format, pivot them longer and mutate the Category_name variable to nicer labels. Keep largest categories separate and aggregate the rest.
You need to manually comment out the categories that are not the largest each year. Check and compare to the previous years largest categories!

Code

exp_totals <- ff_exp %>% rowwise() %>% mutate(exp_TOTALS = sum(across(exp_402:exp_970))) # creates total column too

rev_totals <- ff_rev %>% rowwise() %>%  mutate(rev_TOTALS = sum(across(rev_02:rev_78)))

rev_long_majorcats <- pivot_longer(rev_totals, rev_02:rev_TOTALS, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>% 
  rename(Year = fy) %>%
  mutate(Category_name = case_when(
    Category == "02" ~ "Income Tax" ,
    Category == "03" ~ "Corporate Income Tax" ,
    Category == "06" ~ "Sales Tax" ,
    Category == "09" ~ "Motor Fuel Taxes" ,
    #   Category == "12" ~ "PUBLIC UTILITY TAXES, gross of PPRT" ,
    #  Category == "15" ~ "CIGARETTE TAXES" ,
    #   Category == "18" ~ "LIQUOR GALLONAGE TAXES" ,
    #  Category == "21" ~ "INHERITANCE TAX" ,
    #  Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES, net of refunds " ,
    # Category == "27" ~ "CORP FRANCHISE TAXES & FEES" ,
    #   Category == "30" ~ "HORSE RACING TAXES & FEES",  # in Other
    Category == "31" ~ "Medical Provider Assessments" ,
    #  Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped
    #  Category == "33" ~  "LOTTERY RECEIPTS" ,
    # Category == "35" ~  "OTHER TAXES" ,
    Category == "36" ~  "Receipts from Revenue Producing", 
    Category == "39" ~  "Licenses, Fees, Registration" ,
    # Category == "42" ~  "MOTOR VEHICLE AND OPERATORS" ,
    #    Category == "45" ~  "STUDENT FEES-UNIVERSITIES",   # dropped
    #    Category == "48" ~  "RIVERBOAT WAGERING TAXES" ,
    #  Category == "51" ~  "RETIREMENT CONTRIBUTIONS" , # dropped
    # Category == "54" ~ "GIFTS AND BEQUESTS", 
    Category == "57" ~  "Federal Other" ,
    Category == "58" ~  "Federal Medicaid Reimbursements", 
    Category == "59" ~  "Federal Transportation" ,
    #   Category == "60" ~  "OTHER GRANTS AND CONTRACTS", #other
    #    Category == "63" ~  "INVESTMENT INCOME", # other
    #   Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped
    #   Category == "72" ~ "BOND ISSUE PROCEEDS",  #dropped
    #   Category == "75" ~  "INTER-AGENCY RECEIPTS ",  #dropped
    #   Category == "76" ~  "TRANSFER IN FROM OUT FUNDS",  #other
    # Category == "78new" ~  "ALL OTHER SOURCES" ,
    # Category == "79" ~   "COOK COUNTY IGT", #dropped
    #   Category == "98" ~  "PRIOR YEAR REFUNDS", #dropped
    
    Category == "TOTALS" ~ "Total Revenue",
    T ~ "All Other Sources **" # any other Category number that was not specifically referenced is combined into Other Revenue Sources
    
  ) ) %>% 
  select(-type, -Category) %>%  # drop extra columns type and Category number
  group_by(Year, Category_name) %>%
  summarise(Dollars= round(sum(Dollars),digits=2)) 

# revenue_wide # not actually in wide format yet. 
# has 10 largest rev sources separate and combined all others to Other in long data format. 


# creates wide version of table where each revenue source is a column
revenue_wide_majorcats <- rev_long_majorcats %>% pivot_wider(names_from = Category_name, 
              values_from = Dollars) %>%
  relocate("All Other Sources **", .after = last_col()) %>%
  relocate("Total Revenue", .after =  last_col()) 


exp_long_majorcats <- pivot_longer(exp_totals, exp_402:exp_TOTALS , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>% 
  rename(Year = fy ) %>% 
  mutate(Category_name = 
           case_when(
          #   Category == "402" ~ "Aging" ,
          #  Category == "406" ~ "AGRICULTURE", 
         #    Category == "416" ~ "Central Management",
             Category == "418" ~ "Children & Family Services", 
             Category == "420" ~ "Community Development",
           #  Category == "422" ~ "NATURAL RESOURCES" ,
             Category == "426" ~ "Corrections",
           #  Category == "427" ~ "EMPLOYMENT SECURITY" ,
             Category == "444" ~ "Human Services" ,
           #  Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", 
           #  Category == "482" ~ "PUBLIC HEALTH", 
             Category == "492" ~ "Revenue", 
             Category == "494" ~ "Transportation" ,
           #  Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
             Category == "557" ~ "Tollway" ,
           #  Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", 
            # Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
           #  Category == "900" ~ "NOT IN FRAME",
             Category == "901" ~ "State Pension Contribution",
             Category == "903" ~ "Debt Service",
             Category == "904" ~ "State Employee Healthcare",
           #  Category == "910" ~ "LEGISLATIVE"  ,
          #   Category == "920" ~ "JUDICIAL" ,
         #   Category == "930" ~ "Elected Officers" , 
            # Category == "940" ~ "OTHER HEALTH-RELATED", 
             Category == "941" ~ "Public Safety" ,
           #  Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
           #  Category == "943" ~ "CENTRAL SERVICES",
           #  Category == "944" ~ "BUS & PROFESSION REGULATION" ,
             Category == "945" ~ "Medicaid" ,
          #   Category == "946" ~ "Capital Improvement" , 
           #  Category == "948" ~ "OTHER DEPARTMENTS" ,
            # Category == "949" ~ "OTHER BOARDS & COMMISSIONS" ,
             Category == "959" ~ "K-12 Education" ,
             Category == "960" ~ "University Education",
             Category == "970" ~ "Local Govt Revenue Sharing",
          Category == "TOTALS" ~ "Total Expenditures",
             T ~ "All Other Expenditures **")
           ) %>% 
  select(-type, -Category) %>% 
  group_by(Year, Category_name) %>% 
  summarise(Dollars= round(sum(Dollars),digits=2))

expenditure_wide_majorcats <- exp_long_majorcats %>% 
  pivot_wider(names_from = Category_name, 
              values_from = Dollars) %>%
  relocate("All Other Expenditures **", .after = last_col()) %>%
  relocate("Total Expenditures", .after =  last_col())


# CAGR values for largest expenditure categories and combined All Other Expenditures

# function for calculating the CAGR
calc_cagr <- function(df, n) {
  df <- exp_long_majorcats %>%
    #select(-type) %>%
    arrange(Category_name, Year) %>%
    group_by(Category_name) %>%
    mutate(cagr = ((`Dollars` / lag(`Dollars`, n)) ^ (1 / n)) - 1)

  return(df)
}

# This works for one variable at a time
cagr_25 <- calc_cagr(exp_long_majorcats, 25) %>% 
  # group_by(Category) %>%
  summarize(cagr_25 = round(sum(cagr*100, na.rm = TRUE), 2))

cagr23_precovid <- exp_long_majorcats %>%
  filter(Year <= 2019) %>%
  calc_cagr(21) %>% 
  summarize(cagr_21 = round(sum(cagr*100, na.rm = TRUE), 2))



cagr_10 <- calc_cagr(exp_long_majorcats, 10) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_10 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_5 <- calc_cagr(exp_long_majorcats, 5) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_5 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_3 <- calc_cagr(exp_long_majorcats, 3) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_3 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_2 <- calc_cagr(exp_long_majorcats, 2) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_2 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_1 <- calc_cagr(exp_long_majorcats, 1) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_1 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

CAGR_expenditures_majorcats_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_25 ) %>% 
  select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>% 
  rename("Expenditure Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10,"25-Year CAGR" = cagr_25 )

move_to_last <- function(df, n) df[c(setdiff(seq_len(nrow(df)), n), n), ]

CAGR_expenditures_majorcats_tot <- move_to_last(CAGR_expenditures_majorcats_tot, 1)
CAGR_expenditures_majorcats_tot <- move_to_last(CAGR_expenditures_majorcats_tot, 14) 


CAGR_expenditures_majorcats_tot%>%   
  kbl(caption = "CAGR Calculations for Largest Expenditure Categories" , row.names=FALSE) %>% 
     kable_classic()  %>%
    row_spec(17, bold = T, color = "black", background = "gray")
current_year <- 2023
last_year <- 2022

# Yearly change for Top 13 largest expenditure categories
expenditure_change_majorcats <- exp_long_majorcats %>%
  filter(Year > 2021) %>%
  pivot_wider(names_from = Year , values_from = Dollars,   names_prefix = "Dollars_") %>%
  mutate("FY 2023 ($ Billions)" = round(Dollars_2023/1000, digits = 1),
         "FY 2022 ($ Billions)" = round(Dollars_2022/1000, digits = 1),
         "1-Year Change" = percent((Dollars_2023 -Dollars_2022)/Dollars_2022, accuracy = .1) )  %>%
  left_join(CAGR_expenditures_majorcats_tot, by = c("Category_name" = "Expenditure Category")) %>% 
  arrange(-`FY 2023 ($ Billions)`)%>%
  mutate(`25-Year CAGR` = percent(`25-Year CAGR`/100, accuracy=.1)) %>%
  select(-c(Dollars_2023, Dollars_2022, `1 Year CAGR`:`10 Year CAGR`)) %>%
  rename( "25-Year CAGR" = `25-Year CAGR`, 
          "Expenditure Category" = Category_name )

expenditure_change_majorcats <- move_to_last(expenditure_change_majorcats, 4) 

expenditure_change_majorcats <- move_to_last(expenditure_change_majorcats, 1)

expenditure_change_majorcats %>% 
  kbl(caption = "Yearly Change in Expenditures", row.names = FALSE, align = "l") %>% 
  kable_classic() %>%
    row_spec(17, bold = T, color = "black", background = "gray")

Table 13.3: Largest Expenditure Categories with CAGRs

(a) CAGR Calculations for Largest Expenditure Categories
Expenditure Category	1 Year CAGR	2 Year CAGR	3 Year CAGR	5 Year CAGR	10 Year CAGR	25-Year CAGR
Children & Family Services	25.99	10.03	8.40	7.78	3.33	0.78
Community Development	7.71	-5.46	34.18	24.63	4.34	4.79
Corrections	13.46	2.80	3.47	-2.65	3.13	2.21
Debt Service	-2.89	-1.86	0.07	-0.20	0.80	5.73
Human Services	21.34	16.10	13.77	10.19	4.64	3.29
K-12 Education	9.86	9.86	9.15	7.04	5.35	4.35
Local Govt Revenue Sharing	5.36	23.23	19.08	12.02	6.46	4.68
Medicaid	13.10	11.05	13.29	11.76	8.17	7.43
Public Safety	1.18	-6.87	5.37	14.12	7.58	5.69
Revenue	8.02	8.66	20.73	30.50	17.31	6.39
State Employee Healthcare	-0.09	2.08	0.23	-12.49	3.28	5.82
State Pension Contribution	5.06	10.12	8.85	9.54	9.06	10.52
Tollway	-9.89	-1.78	-0.41	5.23	7.14	6.78
Transportation	16.29	-2.95	7.20	6.92	1.59	3.80
University Education	12.52	7.50	5.01	4.60	0.98	0.81
All Other Expenditures **	14.38	6.63	8.02	6.79	4.16	5.67
Total Expenditures	10.81	9.15	10.67	8.09	5.74	5.19

(b) Yearly Change in Expenditures
Expenditure Category	FY 2023 ($ Billions)	FY 2022 ($ Billions)	1-Year Change	25-Year CAGR
Medicaid	32.4	28.7	13.1%	7.4%
K-12 Education	14.7	13.4	9.9%	4.3%
Local Govt Revenue Sharing	10.9	10.3	5.4%	4.7%
Human Services	8.8	7.3	21.3%	3.3%
State Pension Contribution	6.8	6.5	5.1%	10.5%
Transportation	5.0	4.3	16.3%	3.8%
State Employee Healthcare	3.0	3.0	-0.1%	5.8%
University Education	2.5	2.3	12.5%	0.8%
Debt Service	2.0	2.0	-2.9%	5.7%
Revenue	2.0	1.8	8.0%	6.4%
Tollway	1.9	2.1	-9.9%	6.8%
Corrections	1.7	1.5	13.5%	2.2%
Public Safety	1.7	1.7	1.2%	5.7%
Children & Family Services	1.6	1.3	26.0%	0.8%
Community Development	1.5	1.4	7.7%	4.8%
All Other Expenditures **	14.0	12.2	14.4%	5.7%
Total Expenditures	110.6	99.8	10.8%	5.2%

Top 10 revenue sources CAGRs and Yearly Change Tables:

Code

##### Top 10 revenue CAGRs: ####


calc_cagr <- function(df, n) {
  df <- rev_long_majorcats %>%
    arrange(Category_name, Year) %>%
    group_by(Category_name) %>%
    mutate(cagr = ((Dollars / lag(Dollars, n)) ^ (1 / n)) - 1)

  return(df)
}

# This works for one variable at a time
cagr_25 <- calc_cagr(rev_long_majorcats, 25) %>% 
     # group_by(Category) %>%
  summarize(cagr_25 = round(sum(cagr*100, na.rm = TRUE), 2))

cagr_10 <- calc_cagr(rev_long_majorcats, 10) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_10 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_5 <- calc_cagr(rev_long_majorcats, 5) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_5 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_3 <- calc_cagr(rev_long_majorcats, 3) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_3 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

cagr_2 <- calc_cagr(rev_long_majorcats, 2) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_2 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

 cagr_1 <- calc_cagr(rev_long_majorcats, 1) %>% 
  filter(Year == 2023) %>%
  summarize(cagr_1 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2)))

CAGR_revenue_majorcats_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_25) %>%   
  select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>% 
  rename("Revenue Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10, "25-Year CAGR" = cagr_25 )

CAGR_revenue_majorcats_tot <- move_to_last(CAGR_revenue_majorcats_tot,1)
CAGR_revenue_majorcats_tot <- move_to_last(CAGR_revenue_majorcats_tot,11)

CAGR_revenue_majorcats_tot %>% 
  kbl(caption = "CAGR Calculations for Largest Revenue Sources", row.names = FALSE) %>% 
  kable_classic() %>%
    row_spec(12, bold = T, color = "black", background = "gray")

Table 13.4: Top 10 Revenue Sources with CAGRs
Revenue Category	1 Year CAGR	2 Year CAGR	3 Year CAGR	5 Year CAGR	10 Year CAGR	25-Year CAGR
Corporate Income Tax	4.31	35.75	46.02	28.69	9.92	7.57
Federal Medicaid Reimbursements	6.09	7.28	13.44	8.44	9.32	7.47
Federal Other	-43.87	8.68	3.88	13.39	5.67	4.35
Federal Transportation	15.30	-5.74	5.83	5.82	2.17	3.78
Income Tax	-7.27	2.18	7.87	5.60	4.09	5.13
Licenses, Fees, Registration	9.25	2.24	13.23	11.07	6.18	7.94
Medical Provider Assessments	9.50	3.60	5.58	13.15	9.46	8.41
Motor Fuel Taxes	1.51	3.79	3.40	13.47	7.38	2.73
Receipts from Revenue Producing	8.47	5.70	5.99	0.89	2.69	5.21
Sales Tax	4.81	8.00	9.70	6.61	4.68	3.29
All Other Sources **	14.45	14.15	13.77	6.91	4.77	4.30
Total Revenue	-4.37	8.50	11.42	8.93	5.92	5.09

Code

###### Yearly change summary table for Top 10 Revenues #####
revenue_change_majorcats <- rev_long_majorcats %>%
  #select(-c(Category)) %>%
  filter(Year > 2021) %>%
  pivot_wider(names_from = Year , values_from = Dollars,   names_prefix = "Dollars_") %>%
  mutate(
    "FY 2023 ($ billions)" = round(Dollars_2023/1000, digits = 1),
            "FY 2022 ($ billions)" = round(Dollars_2022/1000, digits = 1),

         "1-Year Change" = percent(((Dollars_2023 -Dollars_2022)/Dollars_2022), accuracy = .1)) %>%
  left_join(CAGR_revenue_majorcats_tot, by = c("Category_name" = "Revenue Category")) %>% 
    arrange(-`FY 2023 ($ billions)`)%>%
  #select(-c(Dollars_2021, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>%
  mutate("25-Year Change" = percent(`25-Year CAGR`/100, accuracy=.1)) %>%
  rename("Revenue Category" = Category_name ) %>%
  select(-c(Dollars_2023, Dollars_2022, `1 Year CAGR`:`25-Year CAGR`)) 

revenue_change_majorcats <- move_to_last(revenue_change_majorcats,5)

revenue_change_majorcats <- move_to_last(revenue_change_majorcats,1)

revenue_change_majorcats%>% 
  kbl(caption = "Yearly Change in Revenue for Main Revenue Sources", row.names = FALSE, align = "l") %>% 
   kable_classic() %>%
    row_spec(12, bold = T, color = "black", background = "gray")

Table 13.5: Top 10 Revenue Sources with CAGRs
Revenue Category	FY 2023 ($ billions)	FY 2022 ($ billions)	1-Year Change	25-Year Change
Income Tax	22.1	23.8	-7.3%	5.1%
Federal Medicaid Reimbursements	20.2	19.0	6.1%	7.5%
Sales Tax	16.2	15.4	4.8%	3.3%
Federal Other	10.9	19.4	-43.9%	4.3%
Corporate Income Tax	10.1	9.7	4.3%	7.6%
Medical Provider Assessments	4.1	3.7	9.5%	8.4%
Receipts from Revenue Producing	2.6	2.4	8.5%	5.2%
Motor Fuel Taxes	2.5	2.5	1.5%	2.7%
Federal Transportation	2.1	1.8	15.3%	3.8%
Licenses, Fees, Registration	2.1	1.9	9.2%	7.9%
All Other Sources **	15.2	13.3	14.4%	4.3%
Total Revenue	108.1	113.0	-4.4%	5.1%

Export summary file with Totals

Code

#install.packages("openxlsx")
library(openxlsx)

dataset_names <- list('Aggregate Revenues' = revenue_wide2, 
                      'Aggregate Expenditures' = expenditure_wide2, 

                      
                      'Table 1' = revenue_change_majorcats, #Top categories with yearly change, 23 yr cagr
                      'Table 2' = expenditure_change_majorcats,
                      
                      'Table 1a. AllCats' = revenue_change2,
                      'Table 2a. AllCats' = expenditure_change2,
                      
                      'CAGR Rev-MajorCats' = CAGR_revenue_majorcats_tot, # Categories Match Table 1 in paper
                      'CAGR Exp-MajorCats' = CAGR_expenditures_majorcats_tot, 
                                            
                     # 'Table 1-AllCats' = expenditure_change_allcats,  # All Categories by Year
                    #  'Table 2-AllCats' = revenue_change_allcats,
                      
                    #  'CAGR_Revenue-AllCats' = CAGR_revenue_summary_tot, 
                   #   'CAGR_Expenditures-AllCats' = CAGR_expenditures_summary_tot, 
                      
                      'Fiscal Gap' = year_totals,    # Total Revenue, Expenditure, and Fiscal gap per year
                      
                      'aggregated_totals_long' = aggregated_totals_long # all data in long format. Good for creating pivot tables in Excel
                      )

write.xlsx(dataset_names, file = 'summary_file_FY23_wTotals_Dec292023.xlsx')

Saves main items in one excel file named summary_file.xlsx. Delete eval=FALSE to run on local computer.

14 Drop COVID Dollars

If only sustainable revenues are included in the model, then the federal dollars from the pandemic response (CARES, CRSSA,& ARPA) should be excluded from the calculation of the fiscal gap.

NOTE: I have only dropped revenue with a source name = Federal Stimulus Package. Federal money went into other funds during the beginning of pandemic. All additional money for medicaid reimbursements and healthcare provider funds were not considered “Federal Stimulus Package” in the data and were not dropped.

fund 0628 is essential government support services. Money in the fund is appropriated to cover COVID-19 related expenses. It should be included in our analytical frame based on criteria 2 and6 — the fund supports an important state function about public safety, which would have to be performed even the fund structure were not existed. Public safety is supported by a combination of departments and boards, including IL Emergency Management Agency, which is the administering agency of the fund.
Education Stabilization Fund
ESSER 1, 2, and 3
CSLFRF (State and Local CURE)
Provider Relief Fund
Coronavirus Relief Fund (CRF)
Consolidated Appropriations Act 2020
Families First Coronavirus Response Act
Paycheck Protection Program and Health Care Enhancement Act

Need to recreate ff_exp and ff_rev totals without stimulus dollars.

Code

rev_temp <- rev_temp %>% mutate(covid_dollars = ifelse(source_name_AWM == "FEDERAL STIMULUS PACKAGE",1,0))

rev_temp %>% filter(source_name_AWM == "FEDERAL STIMULUS PACKAGE") %>%
  group_by(fy) %>% summarize(Received = sum(receipts))

Code

# does not include rev_type == 58, medicaid dollars
covid_dollars_rev <- rev_temp %>% filter(covid_dollars==1) # check what was dropped

covid_dollars_rev %>% group_by(fy, agency_name) %>% 
  summarize(receipts = sum(receipts)) %>% 
  pivot_wider(names_from="agency_name", values_from = "receipts") %>% arrange(-fy)

Code

covid_dollars_rev %>% group_by(fy, fund_name_ab) %>% 
  summarize(receipts = sum(receipts)) %>% 
  arrange(-fy) %>%
  pivot_wider(names_from="fy", values_from = "receipts")

Code

K12_ESSER_words <- c("CRRSA","ESSER","EMER R", "EMR R", "CARES", "AMER R", "EMER ED")

ESSER_exp <- exp_temp %>% 
  filter(agency_name == "STATE BOARD OF EDUCATION") %>%
  mutate(ESSERfunds = case_when(
    str_detect(wh_approp_name, "CRRSA") ~ "ESSER",
    str_detect(wh_approp_name, "ESSER") ~ "ESSER",
    str_detect(wh_approp_name, "EMER R") ~ "ESSER",
    str_detect(wh_approp_name, "EMR R") ~ "ESSER",
    str_detect(wh_approp_name, "CARES") ~ "ESSER",
    str_detect(wh_approp_name, "AMER R") ~ "ESSER",
    str_detect(wh_approp_name, "EMER ED") ~ "ESSER",
    
    TRUE ~ 'not_esser')) %>%
  filter(ESSERfunds == "ESSER")
ESSER_exp

Code

rev_temp <- rev_temp %>%  filter(covid_dollars==0) # keeps observations that were not coded as COVID federal funds



ff_rev <- rev_temp %>% 
  group_by(rev_type_new, fy) %>% 
  summarize(sum_receipts = round(sum(receipts, na.rm=TRUE)/1000000 )) %>%
  pivot_wider(names_from = "rev_type_new", values_from = "sum_receipts", names_prefix = "rev_")

ff_rev<- left_join(ff_rev, tax_refund)

ff_rev <- mutate_all(ff_rev, ~replace_na(.,0))


ff_rev <- ff_rev %>%
  mutate(rev_02 = rev_02 - ref_02,
         rev_03 = rev_03 - ref_03,
         rev_06 = rev_06 - ref_06,
         rev_09 = rev_09 - ref_09,
         rev_21 = rev_21 - ref_21,
         rev_24 = rev_24 - ref_24,
         rev_35 = rev_35 - ref_35

         ) %>% 
  select(-c(ref_02:ref_35, rev_99, rev_76, ref_CHECK
            ))

rev_long <- pivot_longer(ff_rev, rev_02:rev_78, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>% 
  rename(Year = fy) %>%
  mutate(Category_name = case_when(
    Category == "02" ~ "INDIVIDUAL INCOME TAX" ,
    Category == "03" ~ "CORPORATE INCOME TAX" ,
    Category == "06" ~ "SALES TAX" ,
    Category == "09" ~ "MOTOR FUEL TAX" ,
    Category == "12" ~ "PUBLIC UTILITY TAX" ,
    Category == "15" ~ "CIGARETTE TAXES" ,
    Category == "18" ~ "LIQUOR GALLONAGE TAXES" ,
    Category == "21" ~ "INHERITANCE TAX" ,
    Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES" ,
    Category == "27" ~ "CORP FRANCHISE TAXES & FEES" ,
    Category == "30" ~ "HORSE RACING TAXES & FEES",  # in Other
    Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" ,
    Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped
    Category == "33" ~  "LOTTERY RECEIPTS" ,
    Category == "35" ~  "OTHER TAXES" ,
    Category == "36" ~  "RECEIPTS FROM REVENUE PRODUCING", 
    Category == "39" ~  "LICENSES, FEES & REGISTRATIONS" ,
    Category == "42" ~  "MOTOR VEHICLE AND OPERATORS" ,
    Category == "45" ~  "STUDENT FEES-UNIVERSITIES",   # dropped
    Category == "48" ~  "RIVERBOAT WAGERING TAXES" ,
    Category == "51" ~  "RETIREMENT CONTRIBUTIONS" , # dropped
    Category == "54" ~ "GIFTS AND BEQUESTS", 
    Category == "57" ~  "FEDERAL OTHER" ,
    Category == "58" ~  "FEDERAL MEDICAID", 
    Category == "59" ~  "FEDERAL TRANSPORTATION" ,
    Category == "60" ~  "OTHER GRANTS AND CONTRACTS", #other
    Category == "63" ~  "INVESTMENT INCOME", # other
    Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped
    Category == "72" ~ "BOND ISSUE PROCEEDS",  #dropped
    Category == "75" ~  "INTER-AGENCY RECEIPTS ",  #dropped
    Category == "76" ~  "TRANSFER IN FROM OUT FUNDS",  #other
    Category == "78" ~  "ALL OTHER SOURCES" ,
    Category == "79" ~   "COOK COUNTY IGT", #dropped
    Category == "98" ~  "PRIOR YEAR REFUNDS", #dropped
                 T ~ "Check Me!"

  ) )%>% 
  mutate(Category_name = str_to_title(Category_name))

ff_exp <- exp_temp %>% 
  group_by(fy, group) %>% 
  summarize(sum_expenditures = round(sum(expenditure, na.rm=TRUE)/1000000 )) %>%
  pivot_wider(names_from = "group", values_from = "sum_expenditures", names_prefix = "exp_")%>%
  
    left_join(debt_keep_yearly) %>%
  mutate(exp_903 = debt_cost) %>%
  left_join(transfers) %>%
  mutate(exp_970 = exp_971 + exp_972  + exp_975 + exp_976)

ff_exp<- ff_exp %>% select(-c(debt_cost, exp_971:exp_976)) # drop unwanted columns

exp_long <- pivot_longer(ff_exp, exp_402:exp_970 , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>% 
  rename(Year = fy ) %>% 
  mutate(Category_name = 
           case_when(
             Category == "402" ~ "AGING" ,
             Category == "406" ~ "AGRICULTURE", 
             Category == "416" ~ "CENTRAL MANAGEMENT",
             Category == "418" ~ "CHILDREN AND FAMILY SERVICES", 
             Category == "420" ~ "COMMERCE AND ECONOMIC OPPORTUNITY",
             Category == "422" ~ "NATURAL RESOURCES" ,
             Category == "426" ~ "CORRECTIONS",
             Category == "427" ~ "EMPLOYMENT SECURITY" ,
             Category == "444" ~ "HUMAN SERVICES" ,
             Category == "448" ~ "Innovation and Technology", # AWM added fy2022
             Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", 
             Category == "482" ~ "PUBLIC HEALTH", 
             Category == "492" ~ "REVENUE", 
             Category == "494" ~ "TRANSPORTATION" ,
             Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
             Category == "557" ~ "IL STATE TOLL HIGHWAY AUTH" ,
             Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", 
             Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
             Category == "900" ~ "NOT IN FRAME",
             Category == "901" ~ "STATE PENSION CONTRIBUTION",
             Category == "903" ~ "DEBT SERVICE",
             Category == "904" ~ "State Employee Healthcare",
             Category == "910" ~ "LEGISLATIVE"  ,
             Category == "920" ~ "JUDICIAL" ,
             Category == "930" ~ "ELECTED OFFICERS" , 
             Category == "940" ~ "OTHER HEALTH-RELATED", 
             Category == "941" ~ "PUBLIC SAFETY" ,
             Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
             Category == "943" ~ "CENTRAL SERVICES",
             Category == "944" ~ "BUS & PROFESSION REGULATION" ,
             Category == "945" ~ "MEDICAID" ,
             Category == "946" ~ "CAPITAL IMPROVEMENT" , 
             Category == "948" ~ "OTHER DEPARTMENTS" ,
             Category == "949" ~ "OTHER BOARDS & COMMISSIONS" ,
             Category == "959" ~ "K-12 EDUCATION" ,
             Category == "960" ~ "UNIVERSITY EDUCATION",
             Category == "970" ~ "Local Govt Transfers",
             T ~ "CHECK ME!")
           ) %>% 
  mutate(Category_name = str_to_title(Category_name))


#exp_long_nototals <- exp_long %>% filter(Category_name != "Totals")


aggregated_totals_long <- rbind(rev_long, exp_long)

Change plots:

Code

year_totals2 <- aggregated_totals_long %>% 
  group_by(type, Year) %>% 
  summarize(Dollars = sum(Dollars, na.rm = TRUE)) %>% 
  pivot_wider(names_from = "type", values_from = Dollars) %>% 
  rename(
         Expenditures = exp,
         Revenue = rev) %>%  
  mutate(`Fiscal Gap` = round(Revenue - Expenditures)) %>% 
  arrange(desc(Year))
# creates variable for the Gap each year

year_totals2 # gap for FY22 changed to 2.3 billion

Code

annotation_billions <- data.frame(
  x = c(2004, 2017, 2019),
  y = c(60, 50, 10),  
  label = c("Expenditures","Revenue", "Fiscal Gap"))

fiscal_gap1 <- year_totals %>%
  ggplot() +
  geom_hline(yintercept = 0) +
  geom_line(aes(x = Year, y = Revenue/1000), color = "Black", lwd=1) +
  geom_line(aes(x = Year, y = Expenditures/1000, linetype = "dashed"), color = "red", lwd=1) +
  geom_line(aes(x = Year, y = `Fiscal Gap`/1000), color = "gray") +
  geom_text(data = annotation_billions, aes(x=x, y=y, label=label))+
  theme_classic() +
  theme(legend.position = "none", #axis.text.y = element_blank(),  
        #axis.ticks.y = element_blank(),
        axis.title.y = element_blank())+
  scale_linetype_manual(values = c("dashed", "dashed")) +
  scale_y_continuous(limits = c(-20, 120), labels = comma)+
  scale_x_continuous(limits=c(1998,2023), expand = c(0,0)) 


fiscal_gap1

Code

fiscal_gap_droppedCURE <- year_totals2 %>%  
  ggplot() +
  geom_hline(yintercept = 0) +
  geom_line(aes(x = Year, y = Revenue/1000), color = "Black", lwd=1) +
  geom_line(aes(x = Year, y = Expenditures/1000, linetype = "dashed"), color = "red", lwd=1) +
  geom_line(aes(x = Year, y = `Fiscal Gap`/1000), color = "gray") +
  geom_text(data = annotation_billions, aes(x=x, y=y, label=label))+
  theme_classic() +
  theme(legend.position = "none",# axis.text.y = element_blank(),
        #axis.ticks.y = element_blank(),
        axis.title.y = element_blank() )+
  scale_linetype_manual(values = c("dashed", "dashed")) +
  scale_y_continuous(limits=c(-20,120), labels = comma)+
  scale_x_continuous(limits=c(1998, 2023), expand = c(0,0)) 

  # # geom_smooth adds regression line, graphed first so it appears behind line graph
  # geom_smooth(aes(x = Year, y = Revenue), color = "gray", method = "lm", se = FALSE) + 
  # geom_smooth(aes(x = Year, y = Expenditures), color = "rosybrown2", method = "lm", se = FALSE) +
  # 
  # # line graph of revenue and expenditures
  # geom_line(aes(x = Year, y = Revenue), color = "black", size=1) +
  # geom_line(aes(x = Year, y = Expenditures), color = "red", size=1) +
  # geom_line(aes(x=Year, y = `Fiscal Gap`), color="gray") +
  # 
  # geom_text(data= annotation, aes(x=x, y = y, label=label))+
  # 
  # # labels
  #   theme_bw() +
  # scale_y_continuous(labels = comma)+
  # xlab("Year") + 
  # ylab("Millions of Dollars")  +
  # ggtitle("Illinois Expenditures and Revenue Totals, 1998-2022")

fiscal_gap_droppedCURE

Compare with and without federal COVID dollars:

Code

library(gridExtra)

cowplot::plot_grid(fiscal_gap1,
                   fiscal_gap_droppedCURE, nrow=1, labels = c("With ARPA State CURE Funds", "Without ARPA State Cure Funds"))

Revenue amounts in millions of dollars:

Code

rev_long %>%
  filter(Year == 2023) %>%
  #mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>%
 # select(-c(Year, `Total Expenditures`)) %>%
  arrange(desc(`Dollars`)) %>%
  ggplot() + 
  geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`))+ 
  coord_flip() +
    theme_bw() +
      labs(title = "Revenues for FY2023")+
    xlab("Revenue Categories") +
  ylab("Millions of Dollars")

14.1 Forecasting attempt

First images use revenue that includes all federal stimulus packages. Revenue projections are skewed heavily due to the large amount of covid money flowing in the past 2 years.

Code

## Revenues
year_totals2 <- year_totals2 %>% 
  arrange(Year)

#ts_rev <- year_totals %>% select(Year, Revenue ) %>% arrange(Year)

tsrev <- ts(year_totals2$Revenue, start ="1998", frequency = 1) # yearly data

# start(tsrev) # 1998, January
# end(tsrev)  ## 2022 
# summary(tsrev)
# plot(tsrev)
# abline(reg=lm(tsrev~time(tsrev)))


#### ARIMAs
mymodel <- auto.arima(tsrev, seasonal = FALSE)
# mymodel          # ARIMA (0, 1, 0) with drift
myforecastrev <- forecast(mymodel, h = 20)
#plot(myforecastrev,  xlab ="", ylab ="Total Revenue", main ="Chicago Revenue")




#### revenue chart
model_rev <- auto.arima(tsrev, seasonal = FALSE)
forecast_rev <- forecast(model_rev, h = 20)

q <- forecast(forecast_rev,  h = 20) %>% 
  autoplot() +
    ylab("Dollars (Millions)") +
  xlab("Year") +
  ggtitle("Forecasted Revenue") +
  theme_classic() +
  scale_y_continuous(labels = dollar )

summary(forecast_rev)

# annotation <- data.frame(
#   x = c(2027, 2032),
#   y = c(200000, 300000),  
#   label = c("$120 billion in 2027","$135 billion in 2032")
# )

annotation <- data.frame(
  x = c(2020, 2032),
  y = c(150000, 200000),  
  label = c("$120 billion in 2027","$135 billion in 2032")
)

q+ geom_label(data = annotation, aes(x=x, y=y, label=label), size = 3) +
  labs(caption = "after dropping federal covid dollars")


## Expenditures
tsexp <- ts(year_totals2$Expenditures, start = "1998", frequency = 1)
model_exp<- auto.arima(tsexp, seasonal = FALSE)
# model_exp            # ARIMA (0,1,1) with drift

forecast_exp <- forecast(model_exp, h = 20) 
#plot(forecast_exp, xlab ="",  ylab ="Total Expenditures", main ="Chicago Expenditures")

p <- forecast(model_exp,  h = 20) %>% 
  autoplot() +
    ylab("Dollars (Millions)") +
  xlab("Year") +
  ggtitle("Forecasted Expenditures") +
  theme_classic() +
  scale_y_continuous(labels = dollar )

summary(forecast_exp)

annotation <- data.frame(
  x = c(2027, 2032),
  y = c(130000, 100000),  label = c("$117 ± 20 Billion in 2027","$132 ± 26 Billion in 2032 ")
)

p + 
  geom_label(data = annotation, aes(x=x, y=y, label=label), size = 3) + 
  labs(title = "Forecasted Expenditures", 
  caption = "Projected values at 95% confidence interval. 
  Dark blue represents 80% liklihood of falling with that range, 
  light blue represents 95% liklihood of being in projected range.")



## Exp and Rev together

autoplot(tsexp) +
  #geom_line(tsexp)+
  #geom_line(aes(model_rev))+
  autolayer(forecast_rev, series = "Revenue") +
  autolayer(forecast_exp, series = "Expenditure)", alpha = 0.5) +
  geom_line(year_totals, mapping= aes(x = Year, y = Revenue))  + guides(colour = guide_legend("Forecast")) + 
  labs(title = "Forecasted Revenue and Expenditures", caption = "Revenue includes State and Local CURE Dollars")

Revenue forecasting using precovid trends:

Graphing the 3 federal revenue types together may be the most reliable since some COVID funding is still recorded in Federal Other and some are in other categories (like Disaster Response in FY2021). Need to look at more before using.

Code

# does not include rev_type == 58, medicaid dollars
# covid_dollars <- rev_temp %>% filter(covid_dollars==1) # check what was dropped

#covid_dollars %>% group_by(fy,rev_type) %>% summarize(receipts = sum(receipts)) %>% pivot_wider(names_from="rev_type", values_from = "receipts")


rev_temp <- rev_temp %>%  filter(covid_dollars==0) # keeps observations that were not coded as COVID federal funds



ff_rev <- rev_temp %>% 
  group_by(rev_type_new, fy) %>% 
  summarize(sum_receipts = sum(receipts, na.rm=TRUE)/1000000 ) %>%
  pivot_wider(names_from = "rev_type_new", values_from = "sum_receipts", names_prefix = "rev_")

ff_rev<- left_join(ff_rev, tax_refund)

ff_rev <- mutate_all(ff_rev, ~replace_na(.,0))


ff_rev <- ff_rev %>%
  mutate(rev_02 = rev_02 - ref_02,
         rev_03 = rev_03 - ref_03,
         rev_06 = rev_06 - ref_06,
         rev_09 = rev_09 - ref_09,
         rev_21 = rev_21 - ref_21,
         rev_24 = rev_24 - ref_24,
         rev_35 = rev_35 - ref_35

         ) %>% 
  select(-c(ref_02:ref_35, rev_99, rev_76
            ,ref_CHECK
            ))

rev_long <- pivot_longer(ff_rev, rev_02:rev_78, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>% 
  rename(Year = fy) %>%
  mutate(Category_name = case_when(
    Category == "02" ~ "INDIVIDUAL INCOME TAX" ,
    Category == "03" ~ "CORPORATE INCOME TAX" ,
    Category == "06" ~ "SALES TAX" ,
    Category == "09" ~ "MOTOR FUEL TAX" ,
    Category == "12" ~ "PUBLIC UTILITY TAX" ,
    Category == "15" ~ "CIGARETTE TAXES" ,
    Category == "18" ~ "LIQUOR GALLONAGE TAXES" ,
    Category == "21" ~ "INHERITANCE TAX" ,
    Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES" ,
    Category == "27" ~ "CORP FRANCHISE TAXES & FEES" ,
    Category == "30" ~ "HORSE RACING TAXES & FEES",  # in Other
    Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" ,
    Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped
    Category == "33" ~  "LOTTERY RECEIPTS" ,
    Category == "35" ~  "OTHER TAXES" ,
    Category == "36" ~  "RECEIPTS FROM REVENUE PRODUCING", 
    Category == "39" ~  "LICENSES, FEES & REGISTRATIONS" ,
    Category == "42" ~  "MOTOR VEHICLE AND OPERATORS" ,
    Category == "45" ~  "STUDENT FEES-UNIVERSITIES",   # dropped
    Category == "48" ~  "RIVERBOAT WAGERING TAXES" ,
    Category == "51" ~  "RETIREMENT CONTRIBUTIONS" , # dropped
    Category == "54" ~ "GIFTS AND BEQUESTS", 
    Category == "57" ~  "FEDERAL OTHER" ,
    Category == "58" ~  "FEDERAL MEDICAID", 
    Category == "59" ~  "FEDERAL TRANSPORTATION" ,
    Category == "60" ~  "OTHER GRANTS AND CONTRACTS", #other
    Category == "63" ~  "INVESTMENT INCOME", # other
    Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped
    Category == "72" ~ "BOND ISSUE PROCEEDS",  #dropped
    Category == "75" ~  "INTER-AGENCY RECEIPTS ",  #dropped
    Category == "76" ~  "TRANSFER IN FROM OUT FUNDS",  #other
    Category == "78" ~  "ALL OTHER SOURCES" ,
    Category == "79" ~   "COOK COUNTY IGT", #dropped
    Category == "98" ~  "PRIOR YEAR REFUNDS", #dropped
                 T ~ "Check Me!"

  ) )%>% 
  mutate(Category_name = str_to_title(Category_name))



ff_exp <- exp_temp %>% 
  group_by(fy, group) %>% 
  summarize(sum_expenditures = sum(expenditure, na.rm=TRUE)/1000000 ) %>%
  pivot_wider(names_from = "group", values_from = "sum_expenditures", names_prefix = "exp_")%>%
  
    left_join(debt_keep_yearly) %>%
  mutate(exp_903 = debt_cost) %>%
  left_join(transfers) %>%
  mutate(exp_970 = exp_971 + exp_972  + exp_975 + exp_976)

ff_exp<- ff_exp %>% select(-c(debt_cost, exp_971:exp_976)) # drop unwanted columns

exp_long <- pivot_longer(ff_exp, exp_402:exp_970 , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>% 
  rename(Year = fy ) %>% 
  mutate(Category_name = 
           case_when(
             Category == "402" ~ "AGING" ,
             Category == "406" ~ "AGRICULTURE", 
             Category == "416" ~ "CENTRAL MANAGEMENT",
             Category == "418" ~ "CHILDREN AND FAMILY SERVICES", 
             Category == "420" ~ "COMMERCE AND ECONOMIC OPPORTUNITY",
             Category == "422" ~ "NATURAL RESOURCES" ,
             Category == "426" ~ "CORRECTIONS",
             Category == "427" ~ "EMPLOYMENT SECURITY" ,
             Category == "444" ~ "HUMAN SERVICES" ,
             Category == "448" ~ "Innovation and Technology", # AWM added fy2022
             Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", 
             Category == "482" ~ "PUBLIC HEALTH", 
             Category == "492" ~ "REVENUE", 
             Category == "494" ~ "TRANSPORTATION" ,
             Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
             Category == "557" ~ "IL STATE TOLL HIGHWAY AUTH" ,
             Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", 
             Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
             Category == "900" ~ "NOT IN FRAME",
             Category == "901" ~ "STATE PENSION CONTRIBUTION",
             Category == "903" ~ "DEBT SERVICE",
             Category == "904" ~ "State Employee Healthcare",
             Category == "910" ~ "LEGISLATIVE"  ,
             Category == "920" ~ "JUDICIAL" ,
             Category == "930" ~ "ELECTED OFFICERS" , 
             Category == "940" ~ "OTHER HEALTH-RELATED", 
             Category == "941" ~ "PUBLIC SAFETY" ,
             Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
             Category == "943" ~ "CENTRAL SERVICES",
             Category == "944" ~ "BUS & PROFESSION REGULATION" ,
             Category == "945" ~ "MEDICAID" ,
             Category == "946" ~ "CAPITAL IMPROVEMENT" , 
             Category == "948" ~ "OTHER DEPARTMENTS" ,
             Category == "949" ~ "OTHER BOARDS & COMMISSIONS" ,
             Category == "959" ~ "K-12 EDUCATION" ,
             Category == "960" ~ "UNIVERSITY EDUCATION",
             Category == "970" ~ "Local Govt Transfers",
             T ~ "CHECK ME!")
           ) %>% 
  mutate(Category_name = str_to_title(Category_name))


#exp_long_nototals <- exp_long %>% filter(Category_name != "Totals")


aggregated_totals_long <- rbind(rev_long, exp_long)

Change plots:

Code

year_totals2 <- aggregated_totals_long %>% 
  group_by(type, Year) %>% 
  summarize(Dollars = sum(Dollars, na.rm = TRUE)) %>% 
  pivot_wider(names_from = "type", values_from = Dollars) %>% 
  rename(
         Expenditures = exp,
         Revenue = rev) %>%  
  mutate(`Fiscal Gap` = round(Revenue - Expenditures)) %>% 
  arrange(desc(Year))
# creates variable for the Gap each year

year_totals2 # gap for FY22 changed to ~2 or 3 billion

Table 14.1:

Fiscal Gap without State CURE Federal Revenue

Compare with and without federal COVID dollars:

Revenue amounts in millions of dollars:

Code

rev_long %>%
  filter(Year == 2023) %>%
  #mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>%
 # select(-c(Year, `Total Expenditures`)) %>%
  arrange(desc(`Dollars`)) %>%
  ggplot() + 
  geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`))+ 
  coord_flip() +
    theme_bw() +
      labs(title = "Revenues for FY2023")+
    xlab("Revenue Categories") +
  ylab("Millions of Dollars")

Figure 14.2: Comparison of revenue with and without federal State CURE funds.

14.2 All Categories with Totals

Code

exp_totals <- ff_exp %>% rowwise() %>% mutate(exp_TOTALS = sum(across(exp_402:exp_970)))
rev_totals <- ff_rev %>%    rowwise() %>% 
  mutate(rev_TOTALS = sum(across(rev_02:rev_78)))

rev_long <- pivot_longer(rev_totals, rev_02:rev_TOTALS, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>% 
  rename(Year = fy) %>%
  mutate(Category_name = case_when(
    Category == "02" ~ "INDIVIDUAL INCOME TAXES" ,
    Category == "03" ~ "CORPORATE INCOME TAXES" ,
    Category == "06" ~ "SALES TAXES" ,
    Category == "09" ~ "MOTOR FUEL TAX" ,
    Category == "12" ~ "PUBLIC UTILITY TAXES" ,
    Category == "15" ~ "CIGARETTE TAXES" ,
    Category == "18" ~ "LIQUOR GALLONAGE TAXES" ,
    Category == "21" ~ "INHERITANCE TAX" ,
    Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES" ,
    Category == "27" ~ "CORP FRANCHISE TAXES & FEES" ,
    Category == "30" ~ "HORSE RACING TAXES & FEES",  # in Other
    Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" ,
    Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped
    Category == "33" ~  "LOTTERY RECEIPTS" ,
    Category == "35" ~  "OTHER TAXES" ,
    Category == "36" ~  "RECEIPTS FROM REVENUE PRODUCING", 
    Category == "39" ~  "LICENSES, FEES & REGISTRATIONS" ,
    Category == "42" ~  "MOTOR VEHICLE AND OPERATORS" ,
    Category == "45" ~  "STUDENT FEES-UNIVERSITIES",   # dropped
    Category == "48" ~  "RIVERBOAT WAGERING TAXES" ,
    Category == "51" ~  "RETIREMENT CONTRIBUTIONS" , # dropped
    Category == "54" ~ "GIFTS AND BEQUESTS", 
    Category == "57" ~  "FEDERAL OTHER" ,
    Category == "58" ~  "FEDERAL MEDICAID", 
    Category == "59" ~  "FEDERAL TRANSPORTATION" ,
    Category == "60" ~  "OTHER GRANTS AND CONTRACTS", #other
    Category == "63" ~  "INVESTMENT INCOME", # other
    Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped
    Category == "72" ~ "BOND ISSUE PROCEEDS",  #dropped
    Category == "75" ~  "INTER-AGENCY RECEIPTS ",  #dropped
    Category == "76" ~  "TRANSFER IN FROM OUT FUNDS",  #other
    Category == "78" ~  "ALL OTHER SOURCES" ,
    Category == "79" ~   "COOK COUNTY IGT", #dropped
    Category == "98" ~  "PRIOR YEAR REFUNDS", #dropped
Category == "TOTALS" ~ "Total"

  ) ) %>% 
  select(-type, -Category) %>%  # drop extra columns type and Category number
  group_by(Year, Category_name) %>%
  summarise(Dollars= round(sum(Dollars),digits=2)) %>% 
  mutate(Category_name = str_to_title(Category_name))

# creates wide version of table where each revenue source is a column
revenue_wide2 <- rev_long %>% pivot_wider(names_from = Category_name, 
              values_from = Dollars) %>%
#  relocate("Other Revenue Sources **", .after = last_col()) %>%
  relocate("Total", .after =  last_col())

?(caption)

Code

exp_long <- pivot_longer(exp_totals, exp_402:exp_TOTALS , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>% 
  rename(Year = fy ) %>% 
  mutate(Category_name = 
           case_when(
            Category == "402" ~ "AGING" ,
            Category == "406" ~ "AGRICULTURE", 
             Category == "416" ~ "Central Management",
            Category == "418" ~ "CHILDREN AND FAMILY SERVICES", 
             Category == "420" ~ "Community Development",
            Category == "422" ~ "NATURAL RESOURCES" ,
             Category == "426" ~ "CORRECTIONS",
            Category == "427" ~ "EMPLOYMENT SECURITY" ,
             Category == "444" ~ "Human Services" ,
           Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", 
            Category == "482" ~ "PUBLIC HEALTH", 
            Category == "492" ~ "REVENUE", 
             Category == "494" ~ "Transportation" ,
             Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" ,
             Category == "557" ~ "Tollway" ,
             Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", 
             Category == "691" ~ "IL STUDENT ASSISTANCE COMM" ,
             Category == "900" ~ "NOT IN FRAME",
             Category == "901" ~ "State Pension Contribution",
             Category == "903" ~ "Debt Service",
             Category == "904" ~ "State Employee Healthcare",
             Category == "910" ~ "LEGISLATIVE"  ,
             Category == "920" ~ "JUDICIAL" ,
             Category == "930" ~ "ELECTED OFFICERS" , 
             Category == "940" ~ "OTHER HEALTH-RELATED", 
             Category == "941" ~ "Public Safety" ,
             Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" ,
             Category == "943" ~ "CENTRAL SERVICES",
             Category == "944" ~ "BUS & PROFESSION REGULATION" ,
             Category == "945" ~ "Medicaid" ,
             Category == "946" ~ "Capital Improvement" , 
             Category == "948" ~ "OTHER DEPARTMENTS" ,
             Category == "949" ~ "OTHER BOARDS & COMMISSIONS" ,
             Category == "959" ~ "K-12 Education" ,
             Category == "960" ~ "UNIVERSITY EDUCATION",
             Category == "970" ~ "Local Govt Revenue Sharing",
          Category == "TOTALS" ~ "Total") #,T ~ "All Other Expenditures **")
           ) %>% 
  select(-type, -Category) %>% 
  group_by(Year, Category_name) %>% 
  summarise(Dollars= round(sum(Dollars),digits=2)) %>% 
  mutate(Category_name = str_to_title(Category_name))

expenditure_wide2 <- exp_long%>% 
  pivot_wider(names_from = Category_name, 
              values_from = Dollars) %>%
  #relocate("All Other Expenditures **", .after = last_col()) %>%
  relocate("Total", .after =  last_col())

# Calculating the Fiscal Gap {#sec-all-code} ```{r setup, warning=FALSE, message=FALSE} library(tidyverse) library(haven) library(formatR) library(lubridate) library(smooth) library(forecast) library(scales) library(kableExtra) library(ggplot2) library(readxl) library(tidyverse) library(data.table) library(quantmod) library(geofacet) library(janitor) library(cmapplot) theme_set(theme_classic() ) knitr::opts_chunk$set(echo = TRUE, warning = FALSE, message = FALSE) exp_temp <- read_csv("./data/exp_temp.csv") rev_temp <- read_csv("./data/rev_temp.csv") ``` ## Modify Expenditure File ### Tax refunds Aggregate expenditures: Save tax refunds as negative revenue. Code refunds to match the rev_type codes (02=income taxes, 03 = corporate income taxes, 06=sales tax, 09=motor fuel tax, 24=insurance taxes and fees, 35 = all other tax refunds). ```{r} #| label: fig-tax-refunds #| fig-cap: Tax Refunds ## negative revenue becomes tax refunds tax_refund_long <- exp_temp %>% # fund != "0401" # removes State Trust Funds filter(fund != "0401" & (object=="9910"|object=="9921"|object=="9923"|object=="9925")) %>% # keeps these objects which represent revenue, insurance, treasurer,and financial and professional reg tax refunds mutate(refund = case_when( fund=="0278" & sequence == "00" ~ "02", # for income tax refund fund=="0278" & sequence == "01" ~ "03", # tax administration and enforcement and tax operations become corporate income tax refund fund == "0278" & sequence == "02" ~ "02", object=="9921" ~ "21", # inheritance tax and estate tax refund appropriation object=="9923" ~ "09", # motor fuel tax refunds obj_seq_type == "99250055" ~ "06", # sales tax refund fund=="0378" & object=="9925" ~ "24", # insurance privilege tax refund (fund=="0001" & object=="9925") | (object=="9925" & fund == "0384" & fy == 2023) ~ "35", # all other taxes # fund=="0001" & object=="9925" ~ "35", # all other taxes T ~ "CHECK")) # if none of the items above apply to the observations, then code them as CHECK exp_temp <- left_join(exp_temp, tax_refund_long) %>% mutate(refund = ifelse(is.na(refund),"not refund", as.character(refund))) tax_refund <- tax_refund_long %>% group_by(refund, fy)%>% summarize(refund_amount = sum(expenditure, na.rm = TRUE)/1000000) %>% pivot_wider(names_from = refund, values_from = refund_amount, names_prefix = "ref_") %>% mutate_all(~replace_na(.,0)) %>% arrange(fy) tax_refund %>% pivot_longer( ref_02:ref_35, names_to = "Refund Type", values_to = "Amount") %>% ggplot()+ theme_classic()+ geom_line(aes(x=fy,y=Amount, group = `Refund Type`, color = `Refund Type`))+ labs(title = "Refund Types", caption = "Refunds are excluded from Expenditure totals and instead subtracted from Revenue totals") + labs(title = "Tax refunds", caption = "Rev_type codes: 02=income taxes, 03=corporate income taxes, 06=sales tax, 09=motor fuel tax, 24=insurance taxes and fees, 35 = all other tax refunds." ) # remove the items we recoded in tax_refund_long exp_temp <- exp_temp %>% filter(refund == "not refund") ``` `tax_refund` amounts are removed from expenditure totals and subtracted from revenue totals (since they were tax refunds). ### Pension Expenditures State pension contributions are largely captured with object=4431. **(State payments into pension fund).** State payments to the following pension systems: - Teachers Retirement System (TRS) - New POB bond in 2019: Accelerated Bond Fund paid benefits in advance as lump sum - State Employee Retirement System (SERS) - State University Retirement System (SURS) - Judges Retirement System (JRS) - General Assembly Retirement System (GARS) Modify exp_temp and move all state pension contributions to their own group (901). For more information on the variables included or excluded, please see @sec-pensions. ```{r fig-pensions, fig.cap="Pensions"} exp_temp <- exp_temp %>% arrange(fund) %>% mutate(pension = case_when( (object=="4431") ~ 1, # 4431 = easy to find pension payments INTO fund # (object>"1159" & object<"1166") & fund != "0183" & fund != "0193" ~ 2, # objects 1159 to 1166 are all considered Retirement by Comptroller, # Excluded - employer contributions from agencies/organizations/etc. (object=="1298" & # Purchase of Investments, Normally excluded (fy==2010 | fy==2011) & (fund=="0477" | fund=="0479" | fund=="0481")) ~ 3, #judges retirement OUT of fund # state borrowed money from pension funds to pay for core services during 2010 and 2011. # used to fill budget gap and push problems to the future. fund == "0319" ~ 4, # pension stabilization fund TRUE ~ 0) ) table(exp_temp$pension) exp_temp %>% filter(pension != 0) %>% mutate(pension = as.factor(pension))%>% group_by(fy, pension) %>% summarize(expenditure = sum(expenditure, na.rm = TRUE)) %>% ggplot(aes(x=fy, y = expenditure, group=pension)) + theme_classic()+ geom_col(aes(fill = pension)) + labs (title = "Pension expenditures", caption = "1 = State contributions INTO pension funds. 3 = Purchase of Investments anomoly in 2010 and 2011. 4 = pension stabilization fund")+ theme(legend.position = "bottom") ``` ```{r fig-pensions-POB, fig.cap="Pension Expenditures"} # special accounting of pension obligation bond (POB)-funded contributions to JRS, SERS, GARS, TRS exp_temp <- exp_temp %>% # change object for 2010 and 2011, retirement expenditures were bond proceeds and would have been excluded mutate(object = ifelse((pension >0 & in_ff == "0"), "4431", object)) %>% # changes weird teacher & judge retirement system pensions object to normal pension object 4431 mutate(pension = ifelse(pension >0 & in_ff == "0", 6, pension)) %>% # coded as 6 if it was supposed to be excluded. mutate(in_ff = ifelse(pension>0, "1", in_ff)) table(exp_temp$pension) # all other pensions objects codes get agency code 901 for State Pension Contributions exp_temp <- exp_temp %>% mutate(agency = ifelse(pension>0, "901", as.character(agency)), agency_name = ifelse(agency == "901", "State Pension Contributions", as.character(agency_name))) exp_temp %>% filter(pension > 0) %>% mutate(pension = as.factor(pension)) %>% group_by(fy, pension) %>% summarize(expenditure = sum(expenditure, na.rm=TRUE)) %>% ggplot(aes(x=fy, y=expenditure, color = pension)) + geom_line() + theme_classic()+ labs (title = "Pension Expenditures", caption = "") exp_temp %>% filter(pension > 0) %>% group_by(fy) %>% summarize(expenditure = sum(expenditure, na.rm=TRUE)) %>% ggplot(aes(x=fy, y=expenditure)) + geom_line() + theme_classic()+ labs (title = "Pension Expenditures") ``` ### Drop Interfund transfers Drop all cash transfers between funds, statutory transfers, and purchases of investments from expenditure data. - object == 1993 is for interfund cash transfers - agency == 799 is for statutory transfers - object == 1298 is for purchase of investments and is not spending EXCEPT for costs in 2010 and 2011 (and were recoded already to object == "4431"). Over 168,000 observations remain. - 153,889 observations on 1/23/2022? ```{r drop-transfers} transfers_drop <- exp_temp %>% filter( agency == "799" | # statutory transfers object == "1993" | # interfund cash transfers object == "1298") # purchase of investments transfers_drop # items being dropped, # always check to make sure you aren't accidently dropping something of interest. exp_temp <- anti_join(exp_temp, transfers_drop) exp_temp ``` ### State employee healthcare costs Coding healthcare costs was quite difficult. Over the years, State employee healthcare has been within Central Management Bureau of Benefits and Healthcare & Family Services. If observation is a group insurance contribution, then the expenditure amount is set to \$0 (essentially dropped from analysis). Agency 416 had group insurance contributions for 1998-2005 and 2013-present. Agency 478 had group insurance contributions from 2006-2012. FY2021 and FY2022 contributions coded with object = 1900 (lump sum) for some reason?? ```{r eehc1} #if observation is a group insurance contribution, then the expenditure amount is set to $0 (essentially dropped from analysis) # pretend eehc is named group_insurance_contribution or something like that # eehc coded as zero implies that it is group insurance # if eehc=0, then expenditures are coded as zero for group insurance to avoid double counting costs exp_temp <- exp_temp %>% mutate(eehc = ifelse( # group insurance contributions for 1998-2005 and 2013-present fund == "0001" & (object == "1180" | object =="1900") & agency == "416" & appr_org=="20", 0, 1) )%>% mutate(eehc = ifelse( # group insurance contributions for 2006-2012 fund == "0001" & object == "1180" & agency == "478" & appr_org=="80", 0, eehc) )%>% # group insurance contributions from road fund # coded with 1900 for some reason?? mutate(eehc = ifelse( fund == "0011" & object == "1900" & agency == "416" & appr_org=="20", 0, eehc) ) %>% mutate(expenditure = ifelse(eehc=="0", 0, expenditure)) %>% mutate(agency = case_when( # turns specific items into State Employee Healthcare (agency=904) fund=="0907" & (agency=="416" & appr_org=="20") ~ "904", # central management Bureau of benefits using health insurance reserve fund=="0907" & (agency=="478" & appr_org=="80") ~ "904", # agency = 478: healthcare & family services using health insurance reserve - stopped using this in 2012 TRUE ~ as.character(agency))) %>% mutate(agency_name = ifelse( agency == "904", "STATE EMPLOYEE HEALTHCARE", as.character(agency_name)), in_ff = ifelse( agency == "904", 1, in_ff), group = ifelse(agency == "904", "904", as.character(agency))) # creates group variable # Default group = agency number healthcare_costs <- exp_temp %>% filter(group == "904") healthcare_costs exp_temp %>% filter(group == "904") %>% group_by(fy) %>% summarise(healthcare_cost = sum(expenditure, na.rm = TRUE)) %>% ggplot() + geom_line(aes(x=fy, y=healthcare_cost)) + labs(title="State Employee Healthcare Costs - Included in Fiscal Futures Model", caption = "Fund 0907 for agencies responsible for health insurance reserve (DHFS & CMS)") #exp_temp <- anti_join(exp_temp, healthcare_costs) %>% mutate(expenditure = ifelse(object == "1180", 0, expenditure)) #healthcare_costs_yearly <- healthcare_costs %>% group_by(fy, group) %>% summarise(healthcare_cost = sum(expenditure, na.rm = TRUE)/1000000) %>% select(-group) ``` ### Local Transfers Separate transfers to local from parent agencies that come from DOR(492) or Transportation (494). Treats muni revenue transfers as expenditures, not negative revenue. The share of certain taxes levied state-wide at a common rate and then transferred to local governments. (Purely local-option taxes levied by specific local governments with the state acting as collection agent are NOT included.) The six corresponding revenue items are: - Local share of Personal Income Tax - Individual Income Tax Pass-Through New 2021 (source 2582). - Local share of General Sales Tax - Personal Property Replacement Tax on Business Income - Personal Property Replacement Tax on Public Utilities - Local share of Motor Fuel Tax - Transportation Renewal Fund 0952 Until Dec 18. 2022, Local CURE was being aggregated into Revenue totals since the agency was the Department of Revenue. However the \$371 million expenditure is for "LOC GOVT ARPA" and the revenue source that is Local CURE is also \$371 million. Since it cancels out and is just passed through the state government, I am changing changing the fund_ab_in file so that in_ff=0 for the Local CURE fund. It also inflates the department of revenue expenditures in a misleading way when the expense is actually a transfer to local governments. - Dropping Local CURE fund from analysis results in a \$371 million decrease in the department of Revenue (where the Local Government ARPA transfer money). The appropriation for it was over \$740 million so some will probably be rolled over to FY23 too.\ - In the FY21 New and Reused Funds word document, 0325 Local CURE is described as *"Created as a federal trust fund. The fund is established to receive transfers from either the disaster response and recovery fund or the state cure fund of federal funds received by the state. These transfers, subject to appropriation, will provide for the administration and payment of grants and expense reimbursements to units of local government. Revenues should be under Federal Other and expenditures under Commerce and Economic Opportunity."* - I propose changing it to exclude for both. ```{r transfers-to-local} exp_temp <- exp_temp %>% mutate( agency = case_when(fund=="0515" & object=="4470" & type=="08" ~ "971", # income tax to local governments fund=="0515" & object=="4491" & type=="08" & sequence=="00" ~ "971", # object is shared revenue payments fund=="0802" & object=="4491" ~ "972", #pprt transfer fund=="0515" & object=="4491" & type=="08" & sequence=="01" ~ "976", #gst to local fund=="0627" & object=="4472"~ "976" , # public transportation fund but no observations exist fund=="0648" & object=="4472" ~ "976", # downstate public transportation, but doesn't exist fund=="0515" & object=="4470" & type=="00" ~ "976", # object 4470 is grants to local governments object=="4491" & (fund=="0188"|fund=="0189") ~ "976", fund=="0187" & object=="4470" ~ "976", fund=="0186" & object=="4470" ~ "976", object=="4491" & (fund=="0413"|fund=="0414"|fund=="0415") ~ "975", #mft to local fund == "0952"~ "975", # Added Sept 29 2022 AWM. Transportation Renewal MFT TRUE ~ as.character(agency)), agency_name = case_when(agency == "971"~ "INCOME TAX 1/10 TO LOCAL", agency == "972" ~ "PPRT TRANSFER TO LOCAL", agency == "975" ~ "MFT TO LOCAL", agency == "976" ~ "GST TO LOCAL", TRUE~as.character(agency_name)), group = ifelse(agency>"970" & agency < "977", as.character(agency), as.character(group))) ``` ```{r fig-drop-local-transfers, fig.cap="Drop Transfers from State to Local Governments"} transfers_long <- exp_temp %>% filter(group == "971" |group == "972" | group == "975" | group == "976") transfers_long %>% group_by(agency_name, group, fy) %>% summarize(expenditure = sum(expenditure, na.rm=TRUE) )%>% ggplot() + geom_line(aes(x=fy, y = expenditure, color=agency_name)) + theme_classic()+ theme(legend.position = "bottom", legend.title=element_blank())+ labs(title = "Transfers to Local Governments", caption = "Data Source: Illinois Office of the Comptroller") transfers <- transfers_long %>% group_by(fy, group ) %>% summarize(sum_expenditure = sum(expenditure)/1000000) %>% pivot_wider(names_from = "group", values_from = "sum_expenditure", names_prefix = "exp_" ) exp_temp <- anti_join(exp_temp, transfers_long) dropped_inff_0 <- exp_temp %>% filter(in_ff == 0) exp_temp <- exp_temp %>% filter(in_ff == 1) # drops in_ff = 0 funds AFTER dealing with net-revenue above ``` The Local Transfers from the Personal Property Replacement Tax (fund 802) increased over \$2 billion from corporate income taxes alone. Personal property replacement taxes (PPRT) are revenues collected by the state of Illinois and paid to local governments to replace money that was lost by local governments when their powers to impose personal property taxes on corporations, partnerships, and other business entities were taken away. ### Debt Service Debt Service expenditures include interest payment on both short-term and long-term debt. We do not include escrow or principal payments. **Decision from Sept 30 2022:** We are no longer including short term principal payments as a cost; only interest on borrowing is a cost. Pre FY22 and the FY21 correction, we did include an escrow payment and principle payments as costs but not bond proceeds as revenues. This caused expenditures to be inflated because we were essentially counting debt twice - the principle payment and whatever the money was spent on in other expenditure categories, which was incorrect. ```{r debt-service} debt_drop <- exp_temp %>% filter(object == "8841" | object == "8811") # escrow OR principle #debt_drop %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy) debt_keep <- exp_temp %>% filter(fund != "0455" & (object == "8813" | object == "8800" )) # examine the debt costs we want to include #debt_keep %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy) exp_temp <- anti_join(exp_temp, debt_drop) exp_temp <- anti_join(exp_temp, debt_keep) debt_keep <- debt_keep %>% mutate( agency = ifelse(fund != "0455" & (object == "8813" | object == "8800"), "903", as.character(agency)), group = ifelse(fund != "0455" & (object == "8813" | object == "8800"), "903", as.character(group)), in_ff = ifelse(group == "903", 1, as.character(in_ff))) debt_keep_yearly <- debt_keep %>% group_by(fy, group) %>% summarize(debt_cost = sum(expenditure,na.rm=TRUE)/1000000) %>% select(-group) ``` ### Medicaid **Medicaid.** That portion of the Healthcare and Family Services (or Public Aid in earlier years, agency code 478) budget for Medical (appr_organization code 65) for awards and grants (object codes 4400 and 4900). > State CURE will remain in the Medicaid expenditure category due to the nature of it being federal funds providing public health services and funding to locations that provide public services. - Uses same appropriation name of "HEALTHCARE PROVIDER RELIEF" and fund == 0793 and obj_seq_type == 49000000. So can defend the "mistake" of including healthcare provider relief as Medicaid expenditure. ```{r Medicaid-check, include = FALSE, eval=FALSE} medicaid_check <- exp_temp %>% filter(agency=="478" & (appr_org=="01" | appr_org == "65" | appr_org=="88") & (object=="4900" | object=="4400")) # This includes the State CURE fund medicaid_check %>% group_by(fy) %>% summarize(sum = sum(expenditure)) %>% arrange(-fy) # looks good I think AWM # if we want state CURE as public health or other group number # exp_temp <- exp_temp %>% mutate(group = if_else(fund == "0324" & agency=="478" & appr_org == "65" & object=="4900", "478", as.character(group))) medicaid_check %>% filter(fy>2020) %>% group_by(wh_approp_name,fy) %>% summarize(sum=sum(expenditure)) %>% arrange(wh_approp_name) ``` ### Add Other Fiscal Future group codes ```{r group-codes} exp_temp <- exp_temp %>% #mutate(agency = as.numeric(agency) ) %>% # arrange(agency)%>% mutate( group = case_when( agency>"100"& agency<"200" ~ "910", # legislative agency == "528" | (agency>"200" & agency<"300") ~ "920", # judicial pension>0 ~ "901", # pensions (agency>"309" & agency<"400") ~ "930", # elected officers agency == "586" ~ "959", # create new K-12 group agency=="402" | agency=="418" | agency=="478" | agency=="444" | agency=="482" ~ as.character(agency), # aging, CFS, HFS, human services, public health T ~ as.character(group)) ) %>% mutate(group = case_when( agency=="478" & (appr_org=="01" | appr_org == "65" | appr_org=="88") & (object=="4900" | object=="4400") ~ "945", # separates CHIP from health and human services and saves it as Medicaid agency == "586" & fund == "0355" ~ "945", # 586 (Board of Edu) has special education which is part of medicaid # OLD CODE: agency == "586" & appr_org == "18" ~ "945", # Spec. Edu Medicaid Matching agency=="425" | agency=="466" | agency=="546" | agency=="569" | agency=="578" | agency=="583" | agency=="591" | agency=="592" | agency=="493" | agency=="588" ~ "941", # public safety & Corrections agency=="420" | agency=="494" | agency=="406" | agency=="557" ~ as.character(agency), # econ devt & infra, tollway agency=="511" | agency=="554" | agency=="574" | agency=="598" ~ "946", # Capital improvement agency=="422" | agency=="532" ~ as.character(agency), # environment & nat. resources agency=="440" | agency=="446" | agency=="524" | agency=="563" ~ "944", # business regulation agency=="492" ~ "492", # revenue agency == "416" ~ "416", # central management services agency=="448" & fy > 2016 ~ "416", #add DoIT to central management T ~ as.character(group))) %>% mutate(group = case_when( # agency=="684" | agency=="691" ~ as.character(agency), # moved under higher education in next line. 11/28/2022 AWM agency=="692" | agency=="695" | agency == "684" |agency == "691" | (agency>"599" & agency<"677") ~ "960", # higher education agency=="427" ~ as.character(agency), # employment security agency=="507"| agency=="442" | agency=="445" | agency=="452" |agency=="458" | agency=="497" ~ "948", # other departments # other boards & Commissions agency=="503" | agency=="509" | agency=="510" | agency=="565" |agency=="517" | agency=="525" | agency=="526" | agency=="529" | agency=="537" | agency=="541" | agency=="542" | agency=="548" | agency=="555" | agency=="558" | agency=="559" | agency=="562" | agency=="564" | agency=="568" | agency=="579" | agency=="580" | agency=="587" | agency=="590" | agency=="527" | agency=="585" | agency=="567" | agency=="571" | agency=="575" | agency=="540" | agency=="576" | agency=="564" | agency=="534" | agency=="520" | agency=="506" | agency == "533" ~ "949", # non-pension expenditures of retirement funds moved to "Other Departments" # should have removed pension expenditures already from exp_temp in Pensions step above agency=="131" | agency=="275" | agency=="589" |agency=="593"|agency=="594"|agency=="693" ~ "948", T ~ as.character(group))) %>% mutate(group_name = case_when( group == "416" ~ "Central Management", group == "478" ~ "Healthcare and Family Services", group == "482" ~ "Public Health", group == "900" ~ "NOT IN FRAME", group == "901" ~ "STATE PENSION CONTRIBUTION", group == "903" ~ "DEBT SERVICE", group == "910" ~ "LEGISLATIVE" , group == "920" ~ "JUDICIAL" , group == "930" ~ "ELECTED OFFICERS" , group == "940" ~ "OTHER HEALTH-RELATED", group == "941" ~ "PUBLIC SAFETY" , group == "942" ~ "ECON DEVT & INFRASTRUCTURE" , group == "943" ~ "CENTRAL SERVICES", group == "944" ~ "BUS & PROFESSION REGULATION" , group == "945" ~ "MEDICAID" , group == "946" ~ "CAPITAL IMPROVEMENT" , group == "948" ~ "OTHER DEPARTMENTS" , group == "949" ~ "OTHER BOARDS & COMMISSIONS" , group == "959" ~ "K-12 EDUCATION" , group == "960" ~ "UNIVERSITY EDUCATION" , group == agency ~ as.character(group), TRUE ~ "Check name"), year = fy) exp_temp %>% filter(group_name == "Check name") #write_csv(exp_temp, "all_expenditures_recoded.csv") ``` ::: callout-important All expenditures recoded but not aggregated: Allows for inspection of individual expenditures within larger categories. This stage of the data is extremely useful for investigating how individual items have been coded before they are aggregated into larger categories. ::: ## Modify Revenue data Revenue Categories NOT included in Fiscal Futures:\ - 32. Garnishment-Levies. (State is fiduciary, not beneficiary.)\ - 45. Student Fees-Universities. (Excluded from state-level budget.)\ - 51. Retirement Contributions (of individuals and non-state entities).\ - 66. Proceeds, Investment Maturities. (Not sustainable flow.)\ - 72. Bond Issue Proceeds. (Not sustainable flow.)\ - 75. Inter-Agency Receipts.\ - 79. Cook County Intergovernmental Transfers. (State is not beneficiary.)\ - 98. Prior Year Refunds.\ - 99. Statutory Transfers. **All Other Sources** Expanded to include the following smaller sources:\ - 30. Horse Racing Taxes & Fees.\ - 60. Other Grants and Contracts.\ - 63. Investment Income. For aggregating revenue, use the rev_1998_2022 dataframe, join the funds_ab_in_2022 file to it, and then join the ioc_source_type file to the dataset. Remember: You need to update the funds_ab_in and ioc_source_type file every year! ```{r rev-recode-agencies, warning = FALSE, message=FALSE} # recodes old agency numbers to consistent agency number rev_temp <- rev_temp %>% mutate(agency = case_when( (agency=="438"| agency=="475" |agency == "505") ~ "440", # financial institution & professional regulation & # banks and real estate --> coded as financial and professional reg agency == "473" ~ "588", # nuclear safety moved into IEMA (agency =="531" | agency =="577") ~ "532", # coded as EPA (agency =="556" | agency == "538") ~ "406", # coded as agriculture agency == "560" ~ "592", # IL finance authority (fire trucks and agriculture stuff)to state fire marshal agency == "570" & fund == "0011" ~ "494", # city of Chicago road fund to transportation TRUE ~ (as.character(agency)))) ``` ### Federal to State Transfers For an deeper look at federal revenue to Illinois, [Chapter -@sec-covid-federal-funds]. ```{r fig-create-rev-federal-transfers} #rev_temp <- rev_temp %>% filter(in_ff==1) rev_temp <- rev_temp %>% mutate( rev_type = ifelse(rev_type=="57" & agency=="478" & (source=="0618"|source=="2364"|source=="0660"|source=="1552"| source=="2306"| source=="2076"|source=="0676"|source=="0692"), "58", rev_type), rev_type_name = ifelse(rev_type=="58", "Federal Medicaid Reimbursements", rev_type_name), rev_type = ifelse(rev_type=="57" & agency=="494", "59", rev_type), rev_type_name = ifelse(rev_type=="59", "Federal Transportation", rev_type_name), rev_type_name = ifelse(rev_type=="57", "Federal - Other", rev_type_name), rev_type = ifelse(rev_type=="6", "06", rev_type), rev_type = ifelse(rev_type=="9", "09", rev_type)) rev_temp %>% filter(rev_type == "58" | rev_type == "59" | rev_type == "57") %>% group_by(fy, rev_type, rev_type_name) %>% summarise(receipts = sum(receipts, na.rm = TRUE)/1000000) %>% ggplot() + geom_recessions(xformay = "numeric",text = FALSE)+ geom_line(aes(x=fy, y=receipts,color=rev_type_name)) + theme_classic() + scale_y_continuous(labels = comma)+ labs(title = "Federal to State Transfers", y = "Millions of Dollars", x = "") + theme(legend.position = "bottom", legend.title = element_blank() ) ``` **Dropping State CURE Revenue** The Fiscal Futures model focuses on sustainable revenue sources. To understand our fiscal gap and outlook, we need to exclude these one time revenues. GOMB has emphasized that they have allocated COVID dollars to one time expenditures (unemployment trust fund, budget stabilization fund, etc.). The fiscal gap, graphs,and CAGRs have been recalculated in the \[Drop COVID Dollars\] section below. > NOTE: The code chunk below only drops revenue sources with the source name of "Federal Stimulus Package" (which is the State and Local CURE revenue). Additional federal money went into other funds during the beginning of pandemic. Many departments saw increased grants and received other funds (e.g. funds) ```{r} rev_temp <- rev_temp %>% mutate(covid_dollars = ifelse(source_name_AWM == "FEDERAL STIMULUS PACKAGE",1,0)) rev_temp %>% filter(source_name_AWM == "FEDERAL STIMULUS PACKAGE") %>% group_by(fy) %>% summarize(Received = sum(receipts)) ``` ### Health Insurance Premiums from Employees Insurance premiums for employees is coded below but it is NOT used in the fiscal futures model. Employee and employer premiums are considered rev_51 and dropped from analysis in later step. - 0120 = ins prem-option life - 0120 = ins prem-optional life/univ - 0347 = optional health - HMO - 0348 = optional health - dental - 0349 = optional health - univ/local SI - 0350 = optional health - univ/local - 0351 = optional health - retirement - 0352 = optional health - retirement SI - 0353 = optional health - retire/dental - 0354 = optional health - retirement hmo - 2199-2209 = various HMOs, dental, health plans from Health Insurance Reserve (fund) ```{r insurance-premiums} #collect optional insurance premiums to fund 0907 for use in eehc expenditure rev_temp <- rev_temp %>% mutate( #variable not used in aggregates, but could be interesting for other purposes employee_premiums = ifelse(fund=="0907" & (source=="0120"| source=="0121"| (source>"0345" & source<"0357")|(source>"2199" & source<"2209")), 1, 0), # adds more rev_type codes rev_type = case_when( fund =="0427" ~ "12", # pub utility tax fund == "0742" | fund == "0473" ~ "24", # insurance and fees fund == "0976" ~ "36",# receipts from rev producing fund == "0392" |fund == "0723" ~ "39", # licenses and fees fund == "0656" ~ "78", #all other rev sources TRUE ~ as.character(rev_type))) # if not mentioned, then rev_type as it was # # optional insurance premiums = employee insurance premiums # emp_premium <- rev_temp %>% # group_by(fy, employee_premiums) %>% # summarize(employee_premiums_sum = sum(receipts)/1000000) %>% # filter(employee_premiums == 1) %>% # rename(year = fy) %>% # select(-employee_premiums) emp_premium_long <- rev_temp %>% filter(employee_premiums == 1) # 381 observations have employee premiums == 1 # drops employee premiums from revenue # rev_temp <- rev_temp %>% filter(employee_premiums != 1) # should be dropped in next step since rev_type = 51 ``` *Note: In FY21, employee premiums were subtracted from state healthcare costs on the expenditure side to calculate a "Net Healthcare Cost" but that methodology has been discontinued. Totals were practically unchanged: revenue from employee premiums is also very small.* ### Transfers in and Out: Funds that hold and disperse local taxes or fees are dropped from the analysis. Then other excluded revenue types are also dropped. Drops Blank, Student Fees, Retirement contributions, proceeds/investments, bond issue proceeds, interagency receipts, cook IGT, Prior year refunds: ```{r} rev_temp <- rev_temp %>% filter(in_ff == 1) %>% mutate(local = ifelse(is.na(local), 0, local)) %>% # drops all revenue observations that were coded as "local == 1" filter(local != 1) # 1175 doesnt exist? in_from_out <- c("0847", "0867", "1175", "1176", "1177", "1178", "1181", "1182", "1582", "1592", "1745", "1982", "2174", "2264") # what does this actually include: # all are items with rev_type = 75 originally. in_out_df <- rev_temp %>% mutate(infromout = ifelse(source %in% in_from_out, 1, 0)) %>% filter(infromout == 1) rev_temp <- rev_temp %>% mutate(rev_type_new = ifelse(source %in% in_from_out, "76", rev_type)) # if source contains any of the codes in in_from_out, code them as 76 (all other rev). # I end up excluding rev_76 in later steps ``` ```{r droprevtypes} # revenue types to drop drop_type <- c("32", "45", "51", "66", "72", "75", "79", "98") # drops Blank, Student Fees, Retirement contributions, proceeds/investments, # bond issue proceeds, interagency receipts, cook IGT, Prior year refunds. rev_temp <- rev_temp %>% filter(!rev_type_new %in% drop_type) # keep observations that do not have a revenue type mentioned in drop_type table(rev_temp$rev_type_new) rev_temp %>% group_by(fy, rev_type_new) %>% summarize(total_reciepts = sum(receipts)/1000000) %>% pivot_wider(names_from = rev_type_new, values_from = total_reciepts, names_prefix = "rev_") # combines smallest 4 categories to to "Other" # they were the 4 smallest in past years, are they still the 4 smallest? rev_temp <- rev_temp %>% mutate(rev_type_new = ifelse(rev_type=="30" | rev_type=="60" | rev_type=="63" | rev_type=="76", "78", rev_type_new)) #table(rev_temp$rev_type_new) # check work rm(rev_1998_2022) rm(exp_1998_2022) #write.csv(exp_temp, "exp_fy22_recoded_12192022.csv") #write.csv(rev_temp, "rev_fy22_recoded_12192022.csv") ``` ## Pivoting and Merging - Local Government Transfers (exp_970) should be on the expenditure side ### Revenues I chose to drop rev_76 for Transfers in and Out because I do not understand why that step occurs in the previously used Stata code. Rev_76 was created and included in rev_78 for All Other Revenues in old Stata code for years before FY21 but that method has been discontinued for FY22. Including vs excluding rev_76 does not change the overall interpretation of the fiscal gap.  ```{r code-break-check, include=FALSE} rev_temp %>% filter(is.na(rev_type)) ``` ```{r} #| label: tbl-final-ffrev-table #| tbl-cap: "Pivoted Revenue Table ($ Millions) - Intermediate Step" ff_rev <- rev_temp %>% group_by(rev_type_new, fy) %>% summarize(sum_receipts = sum(receipts, na.rm=TRUE)/1000000 ) %>% pivot_wider(names_from = "rev_type_new", values_from = "sum_receipts", names_prefix = "rev_") ff_rev<- left_join(ff_rev, tax_refund) #ff_rev <- left_join(ff_rev, pension2_fy22, by=c("fy" = "year")) #ff_rev <- left_join(ff_rev, eehc2_amt) ff_rev <- mutate_all(ff_rev, ~replace_na(.,0)) # # ff_rev <- ff_rev %>% # mutate(rev_02 = rev_02 - ref_02, # rev_03 = rev_03 - ref_03, # rev_06 = rev_06 - ref_06, # rev_09 = rev_09 - ref_09, # rev_21 = rev_21 - ref_21, # rev_24 = rev_24 - ref_24, # rev_35 = rev_35 - ref_35 # # # rev_78new = rev_78 #+ pension_amt #+ eehc # ) %>% # select(-c(ref_02:ref_35, rev_99, rev_NA, rev_76 # #, ref_CHECK#, pension_amt , rev_76, # # , eehc # )) # # ff_rev #noproblem <- c(0) # if ref_CHECK = $0, then there is no problem. :) if((sum(ff_rev$ref_CHECK) == 0 )){ ff_rev <- ff_rev %>% mutate(rev_02 = rev_02 - ref_02, rev_03 = rev_03 - ref_03, rev_06 = rev_06 - ref_06, rev_09 = rev_09 - ref_09, rev_21 = rev_21 - ref_21, rev_24 = rev_24 - ref_24, rev_35 = rev_35 - ref_35 ) %>% select(-c(ref_02:ref_35, rev_99, rev_76, ref_CHECK )) }else{"You have a problem! Check what revenue items did not have rev codes (causing it to be coded as rev_NA) or the check if there were refunds that were not assigned revenue codes (tax_refunds_long objects)"} ff_rev %>% mutate_all(., ~round(.,digits=0)) ``` Since I already pivot_wider()ed the table in the previous code chunk, I now change each column's name by using rename() to set new variable names. Ideally the final dataframe would have both the variable name and the variable label but I have not done that yet. ```{r} #| label: tbl-labeled-final-ffrev-table #| tbl-cap: Aggregated Revenue Categories ($ Millions), with old labels aggregate_rev_labels <- ff_rev %>% rename("INDIVIDUAL INCOME TAXES, gross of local, net of refunds" = rev_02, "CORPORATE INCOME TAXES, gross of PPRT, net of refunds" = rev_03, "SALES TAXES, gross of local share" = rev_06 , "MOTOR FUEL TAX, gross of local share, net of refunds" = rev_09 , "PUBLIC UTILITY TAXES, gross of PPRT" = rev_12, "CIGARETTE TAXES" = rev_15 , "LIQUOR GALLONAGE TAXES" = rev_18, "INHERITANCE TAX" = rev_21, "INSURANCE TAXES&FEES&LICENSES, net of refunds" = rev_24 , "CORP FRANCHISE TAXES & FEES" = rev_27, # "HORSE RACING TAXES & FEES" = rev_30, # in Other "MEDICAL PROVIDER ASSESSMENTS" = rev_31 , # "GARNISHMENT-LEVIES " = rev_32 , # dropped "LOTTERY RECEIPTS" = rev_33 , "OTHER TAXES" = rev_35, "RECEIPTS FROM REVENUE PRODUCNG" = rev_36, "LICENSES, FEES & REGISTRATIONS" = rev_39 , "MOTOR VEHICLE AND OPERATORS" = rev_42 , # "STUDENT FEES-UNIVERSITIES" = rev_45, # dropped "RIVERBOAT WAGERING TAXES" = rev_48 , # "RETIREMENT CONTRIBUTIONS " = rev_51, # dropped "GIFTS AND BEQUESTS" = rev_54, "FEDERAL OTHER" = rev_57 , "FEDERAL MEDICAID" = rev_58, "FEDERAL TRANSPORTATION" = rev_59 , #"OTHER GRANTS AND CONTRACTS" = rev_60, #other # "INVESTMENT INCOME" = rev_63, # other # "PROCEEDS,INVESTMENT MATURITIES" = rev_66 , #dropped # "BOND ISSUE PROCEEDS" = rev_72, #dropped # "INTER-AGENCY RECEIPTS" = rev_75, #dropped # "TRANSFER IN FROM OUT FUNDS" = rev_76, #other "ALL OTHER SOURCES" = rev_78, # "COOK COUNTY IGT" = rev_79, #dropped # "PRIOR YEAR REFUNDS" = rev_98 #dropped ) aggregate_rev_labels %>% mutate_all(., ~round(., digits = 0)) ``` ### Expenditures Create exp_970 for all local government transfers (exp_971 + exp_972 + exp_975 + exp_976). ```{r} #| label: tbl-ffexp-notlabeled #| tbl-cap: "Pivoted Expenditure Categories ($ Millions)" ff_exp <- exp_temp %>% group_by(fy, group) %>% summarize(sum_expenditures = sum(expenditure, na.rm=TRUE)/1000000 ) %>% pivot_wider(names_from = "group", values_from = "sum_expenditures", names_prefix = "exp_")%>% left_join(debt_keep_yearly) %>% mutate(exp_903 = debt_cost) %>% # left_join(healthcare_costs_yearly) %>% # join state employee healthcare and subtract employee premiums # left_join(emp_premium, by = c("fy" = "year")) %>% # mutate(exp_904_new = (`healthcare_cost` - `employee_premiums_sum`)) %>% # state employee healthcare premiums # left_join(retirement_contributions) %>% # mutate(exp_901_new = exp_901 - contributions/1000000) %>% #employee pension contributions # join local transfers and create exp_970 left_join(transfers) %>% mutate(exp_970 = exp_971 + exp_972 + exp_975 + exp_976) ff_exp<- ff_exp %>% select(-c(debt_cost, exp_971:exp_976)) # drop unwanted columns ff_exp # not labeled ``` ```{r} #| label: tbl-final-table-with-labels #| tbl-cap: "Final Expenditure Categories, with Fiscal Futures Grouped Expenditure Categories" # exp_temp %>% # group_by(year, group) %>% # summarize(sum_expenditure = round(sum(expenditure)/1000000)) %>% # arrange(year) %>% # pivot_wider(names_from = "group", values_from = "sum_expenditure") # aggregate_exp_labeled <- exp_temp %>% group_by(year, group_name) %>% summarize(sum_expenditure = sum(expenditure)/1000000) %>% arrange(year) %>% pivot_wider(names_from = "group_name", values_from = "sum_expenditure") aggregate_exp_labeled %>% mutate_all(., ~round(., digits = 0)) ``` # Graphs and Tables {#sec-graphs-and-tables} Create total revenues and total expenditures only: - after aggregating expenditures and revenues, pivoting wider, then I want to drop the columns that I no longer want and then pivot_longer(). After pivoting_longer() and creating `rev_long` and `exp_long`, expenditures and revenues are in the same format and can be combined together for the totals and gap each year. ```{r rev-exp-totals-long-oldcatnames, eval=FALSE, include=FALSE} rev_long <- pivot_longer(ff_rev, rev_02:rev_78, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>% rename(Year = fy) %>% mutate(Category_name = case_when( Category == "02" ~ "INDIVIDUAL INCOME TAXES, gross of local, net of refunds" , Category == "03" ~ "CORPORATE INCOME TAXES, gross of PPRT, net of refunds" , Category == "06" ~ "SALES TAXES, gross of local share" , Category == "09" ~ "MOTOR FUEL TAX, gross of local share, net of refunds" , Category == "12" ~ "PUBLIC UTILITY TAXES, gross of PPRT" , Category == "15" ~ "CIGARETTE TAXES" , Category == "18" ~ "LIQUOR GALLONAGE TAXES" , Category == "21" ~ "INHERITANCE TAX" , Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES, net of refunds " , Category == "27" ~ "CORP FRANCHISE TAXES & FEES" , Category == "30" ~ "HORSE RACING TAXES & FEES", # in Other Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" , Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped Category == "33" ~ "LOTTERY RECEIPTS" , Category == "35" ~ "OTHER TAXES" , Category == "36" ~ "RECEIPTS FROM REVENUE PRODUCNG", Category == "39" ~ "LICENSES, FEES & REGISTRATIONS" , Category == "42" ~ "MOTOR VEHICLE AND OPERATORS" , Category == "45" ~ "STUDENT FEES-UNIVERSITIES", # dropped Category == "48" ~ "RIVERBOAT WAGERING TAXES" , Category == "51" ~ "RETIREMENT CONTRIBUTIONS" , # dropped Category == "54" ~ "GIFTS AND BEQUESTS", Category == "57" ~ "FEDERAL OTHER" , Category == "58" ~ "FEDERAL MEDICAID", Category == "59" ~ "FEDERAL TRANSPORTATION" , Category == "60" ~ "OTHER GRANTS AND CONTRACTS", #other Category == "63" ~ "INVESTMENT INCOME", # other Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped Category == "72" ~ "BOND ISSUE PROCEEDS", #dropped Category == "75" ~ "INTER-AGENCY RECEIPTS ", #dropped Category == "76" ~ "TRANSFER IN FROM OUT FUNDS", #other Category == "78" ~ "ALL OTHER SOURCES" , Category == "79" ~ "COOK COUNTY IGT", #dropped Category == "98" ~ "PRIOR YEAR REFUNDS", #dropped T ~ "Check Me!" ) )%>% mutate(Category_name = str_to_title(Category_name)) exp_long <- pivot_longer(ff_exp, exp_402:exp_970 , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>% rename(Year = fy ) %>% mutate(Category_name = case_when( Category == "402" ~ "AGING" , Category == "406" ~ "AGRICULTURE", Category == "416" ~ "CENTRAL MANAGEMENT", Category == "418" ~ "CHILDREN AND FAMILY SERVICES", Category == "420" ~ "COMMERCE AND ECONOMIC OPPORTUNITY", Category == "422" ~ "NATURAL RESOURCES" , Category == "426" ~ "CORRECTIONS", Category == "427" ~ "EMPLOYMENT SECURITY" , Category == "444" ~ "HUMAN SERVICES" , Category == "448" ~ "Innovation and Technology", # AWM added fy2022 Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", Category == "482" ~ "PUBLIC HEALTH", Category == "492" ~ "REVENUE", Category == "494" ~ "TRANSPORTATION" , Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" , Category == "557" ~ "IL STATE TOLL HIGHWAY AUTH" , Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", Category == "691" ~ "IL STUDENT ASSISTANCE COMM" , Category == "900" ~ "NOT IN FRAME", Category == "901" ~ "STATE PENSION CONTRIBUTION", Category == "903" ~ "DEBT SERVICE", Category == "904" ~ "State Employee Healthcare", Category == "910" ~ "LEGISLATIVE" , Category == "920" ~ "JUDICIAL" , Category == "930" ~ "ELECTED OFFICERS" , Category == "940" ~ "OTHER HEALTH-RELATED", Category == "941" ~ "PUBLIC SAFETY" , Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" , Category == "943" ~ "CENTRAL SERVICES", Category == "944" ~ "BUS & PROFESSION REGULATION" , Category == "945" ~ "MEDICAID" , Category == "946" ~ "CAPITAL IMPROVEMENT" , Category == "948" ~ "OTHER DEPARTMENTS" , Category == "949" ~ "OTHER BOARDS & COMMISSIONS" , Category == "959" ~ "K-12 EDUCATION" , Category == "960" ~ "UNIVERSITY EDUCATION", Category == "970" ~ "Local Govt Transfers", T ~ "CHECK ME!") ) %>% mutate(Category_name = str_to_title(Category_name)) #write_csv(exp_long, "expenditures_recoded_long_FY23.csv") #write_csv(rev_long, "revenue_recoded_long_FY23.csv") aggregated_totals_long <- rbind(rev_long, exp_long) aggregated_totals_long year_totals <- aggregated_totals_long %>% group_by(type, Year) %>% summarize(Dollars = sum(Dollars, na.rm = TRUE)) %>% pivot_wider(names_from = "type", values_from = Dollars) %>% rename( Expenditures = exp, Revenue = rev) %>% mutate(Gap = Revenue - Expenditures) %>% arrange(desc(Year)) # creates variable for the Gap each year year_totals %>% mutate_all(., ~round(., digits = 0)) #write_csv(aggregated_totals_long, "aggregated_totals.csv") ``` ```{r} #| label: tbl-rev-and-exp-totals-long #| tbl-cap: Long Version of Data that has Revenue and Expenditures in One Dataframe #| tbl-cap-location: top rev_long <- pivot_longer(ff_rev, rev_02:rev_78, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>% rename(Year = fy) %>% mutate(Category_name = case_when( Category == "02" ~ "INDIVIDUAL INCOME TAXES" , Category == "03" ~ "CORPORATE INCOME TAXES" , Category == "06" ~ "SALES TAXES" , Category == "09" ~ "MOTOR FUEL TAX" , Category == "12" ~ "PUBLIC UTILITY TAXES" , Category == "15" ~ "CIGARETTE TAXES" , Category == "18" ~ "LIQUOR GALLONAGE TAXES" , Category == "21" ~ "INHERITANCE TAX" , Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES" , Category == "27" ~ "CORP FRANCHISE TAXES & FEES" , Category == "30" ~ "HORSE RACING TAXES & FEES", # in Other Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" , Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped Category == "33" ~ "LOTTERY RECEIPTS" , Category == "35" ~ "OTHER TAXES" , Category == "36" ~ "RECEIPTS FROM REVENUE PRODUCING", Category == "39" ~ "LICENSES, FEES & REGISTRATIONS" , Category == "42" ~ "MOTOR VEHICLE AND OPERATORS" , Category == "45" ~ "STUDENT FEES-UNIVERSITIES", # dropped Category == "48" ~ "RIVERBOAT WAGERING TAXES" , Category == "51" ~ "RETIREMENT CONTRIBUTIONS" , # dropped Category == "54" ~ "GIFTS AND BEQUESTS", Category == "57" ~ "FEDERAL OTHER" , Category == "58" ~ "FEDERAL MEDICAID", Category == "59" ~ "FEDERAL TRANSPORTATION" , Category == "60" ~ "OTHER GRANTS AND CONTRACTS", #other Category == "63" ~ "INVESTMENT INCOME", # other Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped Category == "72" ~ "BOND ISSUE PROCEEDS", #dropped Category == "75" ~ "INTER-AGENCY RECEIPTS ", #dropped Category == "76" ~ "TRANSFER IN FROM OUT FUNDS", #other Category == "78" ~ "ALL OTHER SOURCES" , Category == "79" ~ "COOK COUNTY IGT", #dropped Category == "98" ~ "PRIOR YEAR REFUNDS", #dropped T ~ "Check Me!" ) )%>% mutate(Category_name = str_to_title(Category_name)) exp_long <- pivot_longer(ff_exp, exp_402:exp_970 , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>% rename(Year = fy ) %>% mutate(Category_name = case_when( Category == "402" ~ "AGING" , Category == "406" ~ "AGRICULTURE", Category == "416" ~ "CENTRAL MANAGEMENT", Category == "418" ~ "CHILDREN AND FAMILY SERVICES", Category == "420" ~ "COMMERCE AND ECONOMIC OPPORTUNITY", Category == "422" ~ "NATURAL RESOURCES" , Category == "426" ~ "CORRECTIONS", Category == "427" ~ "EMPLOYMENT SECURITY" , Category == "444" ~ "HUMAN SERVICES" , Category == "448" ~ "Innovation and Technology", # AWM added fy2022 Category == "478" ~ "FAMILY SERVICES net Medicaid", Category == "482" ~ "PUBLIC HEALTH", Category == "492" ~ "REVENUE", Category == "494" ~ "TRANSPORTATION" , Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" , Category == "557" ~ "IL STATE TOLL HIGHWAY AUTH" , Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", Category == "691" ~ "IL STUDENT ASSISTANCE COMM" , Category == "900" ~ "NOT IN FRAME", Category == "901" ~ "STATE PENSION CONTRIBUTION", Category == "903" ~ "DEBT SERVICE", Category == "904" ~ "State Employee Healthcare", Category == "910" ~ "LEGISLATIVE" , Category == "920" ~ "JUDICIAL" , Category == "930" ~ "ELECTED OFFICERS" , Category == "940" ~ "OTHER HEALTH-RELATED", Category == "941" ~ "PUBLIC SAFETY" , Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" , Category == "943" ~ "CENTRAL SERVICES", Category == "944" ~ "BUS & PROFESSION REGULATION" , Category == "945" ~ "MEDICAID" , Category == "946" ~ "CAPITAL IMPROVEMENT" , Category == "948" ~ "OTHER DEPARTMENTS" , Category == "949" ~ "OTHER BOARDS & COMMISSIONS" , Category == "959" ~ "K-12 EDUCATION" , Category == "960" ~ "UNIVERSITY EDUCATION", Category == "970" ~ "Local Govt Transfers", T ~ "CHECK ME!") ) %>% mutate(Category_name = str_to_title(Category_name)) #write_csv(exp_long, "expenditures_recoded_long_FY23.csv") #write_csv(rev_long, "revenue_recoded_long_FY23.csv") aggregated_totals_long <- rbind(rev_long, exp_long) aggregated_totals_long %>% mutate(`Dollars (Millions)` = round(Dollars, digits = 0)) %>% select(-Dollars) %>% select(Year, Category_name, `Dollars (Millions)`, type, Category) ``` ```{r} #| label: tbl-year-totals-table-withgap #| tbl-cap-location: top year_totals <- aggregated_totals_long %>% group_by(type, Year) %>% summarize(Dollars = sum(Dollars, na.rm = TRUE)) %>% pivot_wider(names_from = "type", values_from = Dollars) %>% rename( Expenditures = exp, Revenue = rev) %>% mutate(`Fiscal Gap` = round(Revenue - Expenditures)) # %>% arrange(desc(Year)) # creates variable for the Gap each year year_totals %>% mutate_all(., ~round(., digits = 0)) %>% kbl(caption = "Fiscal Gap for each Fiscal Year ($ Millions)") %>% kable_styling(bootstrap_options = c("striped")) %>% kable_classic() %>% add_footnote(c("Values include State CURE dollars (SLFRF)")) ``` Graphs made from `aggregated_totals_long` dataframe. ```{r} #| label: fig-fiscal-gap #| fig-cap: "Fiscal Gap Comparison" #| fig-subcap: #| - "Fiscal Gap With Trend Lines" #| - "Fiscal Gap Without Trend Lines" #| fig-cap-location: top annotation <- data.frame( x = c(2004, 2017, 2019), y = c(60, 50, 5), label = c("Expenditures","Revenue", "Fiscal Gap") ) ## Dashed line versions for expenditures: library(cmapplot) fiscal_gap <- ggplot(data = year_totals, aes(x=Year, y = Revenue/1000)) + geom_recessions(text = FALSE)+ # geom_smooth adds regression line, graphed first so it appears behind line graph geom_smooth(aes(x = Year, y = Revenue/1000), color = "gray", alpha = 0.7, method = "lm", se = FALSE) + # scale_linetype_manual(values="dashed")+ geom_smooth(aes(x = Year, y = Expenditures/1000), color ="rosybrown2", linetype = "dotted", method = "lm", se = FALSE, alpha = 0.7) + # line graph of revenue and expenditures geom_line(aes(x = Year, y = Revenue/1000), color = "Black", size=1) + geom_line(aes(x = Year, y = Expenditures/1000, linetype = "dashed"), color = "red", lwd=1) + geom_line(aes(x = Year, y = (`Fiscal Gap`/1000)), color = "darkgray", lwd = 1) + geom_hline(yintercept = 0) + geom_text(data = annotation, aes(x=x, y=y, label=label))+ # labels theme_classic() + theme(legend.position = "none")+ scale_linetype_manual(values = c("dashed", "dashed")) + scale_x_continuous(expand = c(0,0)) + # scale_y_continuous(labels = comma)+ xlab("Year") + ylab("Billions of Dollars") + ggtitle("Illinois Expenditures and Revenue Totals, 1998-2023") fiscal_gap # annotation_billions <- data.frame( # x = c(2004, 2017, 2019), # y = c(60, 50, 5), # label = c("Expenditures","Revenue", "Fiscal Gap")) fiscal_gap2 <-ggplot(data = year_totals, aes(x=Year, y = Revenue/1000)) + geom_recessions(text = FALSE)+ geom_line(aes(x = Year, y = Revenue/1000), color = "Black", lwd=1) + geom_line(aes(x = Year, y = Expenditures/1000, linetype = "dashed"), color = "red", lwd=1) + geom_line(aes(x = Year, y = (`Fiscal Gap`/1000)), color = "darkgray", lwd=1) + geom_text(data = annotation, aes(x=x, y=y, label=label))+ theme_classic() + theme(legend.position = "none")+ scale_linetype_manual(values = c("dashed", "dashed")) + geom_hline(yintercept = 0) + scale_y_continuous(labels = comma)+ scale_x_continuous(expand = c(0,0)) + xlab("Year") + ylab("Billions of Dollars") + ggtitle("Illinois Expenditures and Revenue Totals, 1998-2023") fiscal_gap2 ggsave(plot = fiscal_gap2, filename= "Figure1-fiscalgap.eps") ``` **Expenditure and revenue amounts in billions of dollars:** ```{r} #| label: fig-bar-graphs-fy23 #| fig-cap: FY23 Totals #| fig-subcap: #| - "FY23 Expenditures" #| - "FY23 Revenue Sources" exp_long %>% filter(Year == 2023) %>% #mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>% # select(-c(Year, `Total Expenditures`)) %>% arrange(desc(`Dollars`)) %>% ggplot() + geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = (`Dollars`/1000), fill = "red"))+ coord_flip() + theme_classic()+ theme(legend.position = "none") + labs(title = "Expenditures for FY2023") + xlab("Expenditure Categories") + ylab("Billions of Dollars") rev_long %>% filter(Year == 2023) %>% #mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>% # select(-c(Year, `Total Expenditures`)) %>% arrange(desc(`Dollars`)) %>% ggplot() + geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = (`Dollars`/1000)))+ coord_flip() + theme_classic() + theme(legend.position = "none") + labs(title = "Revenues for FY2023")+ xlab("Revenue Categories") + ylab("Billions of Dollars") ``` ```{r, eval=FALSE, include = FALSE} # Both graphs include Total revenue and expenditures as a bar on the top. exp_long %>% filter(Year == 2023) %>% #mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>% # select(-c(Year, `Total Expenditures`)) %>% arrange(desc(`Dollars`)) %>% ggplot() + geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`))+ coord_flip() + labs(title = "Expenditures for FY2023") + xlab("Expenditure Categories") + ylab("Millions of Dollars") + theme_classic() rev_long %>% filter(Year == 2023) %>% #mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>% # select(-c(Year, `Total Expenditures`)) %>% arrange(desc(`Dollars`)) %>% ggplot() + geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`))+ coord_flip() + xlab("Revenue Categories") + ylab("Millions of Dollars") + theme_classic() ``` **Expenditure and revenues when focusing on largest categories and combining others into "All Other Expenditures(Revenues)":** ```{r} #| label: fig-bargraphs-largest-expandrev-cats #| fig-cap: #| fig-subcap: #| - "Largest Expenditures for FY2023" #| - "Largest Revenue Sources for FY2023" exp_long %>% filter( Year == 2023) %>% mutate(rank = rank(Dollars), Category_name = ifelse(rank > 13, Category_name, 'All Other Expenditures')) %>% # select(-c(Year, Dollars, rank)) %>% arrange(desc(Dollars)) %>% ggplot() + geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`/1000), fill = "rosybrown2")+ coord_flip() + theme_classic() + labs(title = "Expenditures for FY2023") + xlab("") + ylab("Billions of Dollars") rev_long %>% filter( Year == 2023) %>% mutate(rank = rank(Dollars), Category_name = ifelse(rank > 10, Category_name, 'All Other Sources')) %>% arrange(desc(Dollars)) %>% ggplot() + geom_col(aes(x = fct_reorder(Category_name, `Dollars`/1000), y = `Dollars`/1000), fill = "dark gray")+ coord_flip() + theme_classic() + labs(title = "Revenues for FY2023") + xlab("") + ylab("Billions of Dollars") ``` **Changes in Categories - 2022 to 2023** ```{r} #| label: fig-dotplots #| fig-cap: Change from FY22 to FY23 #| fig-subcap: #| - "Change in Revenue Sources, FY22 to FY23" #| - "Change in Expenditure Categories, FY22 to FY23" #| fig-cap-location: top rev_dotplot <- rev_long %>% filter(Year == "2023" | Year == "2022") %>% mutate(Year = as.character(Year)) %>% ggplot(aes(x = Dollars/1000, y = reorder(Category, Dollars))) + geom_line(aes(group = Category) )+ geom_text(aes(x = ifelse(Year == "2023", as.numeric(Dollars)/1000, NA), label = ifelse(Year == "2023", Category_name, "")), hjust = -0.2, size = 2.8) + geom_point(aes(color = Year), size=2) + labs(title = "2022 to 2023 Change in Revenue", x = "Billions of Dollars" , y = "", caption = "") + scale_fill_manual(values = c("#d62828", "#003049"), labels = c("FY 2022", "FY 2023"))+ scale_color_manual(values = c("#d62828", "#003049")) + theme_classic()+ theme( legend.position = "bottom" , axis.text.y = element_blank(), axis.ticks.y = element_blank(), axis.line.y.left = element_blank())+ scale_x_continuous(limits = c(0, 35), labels = comma) rev_dotplot ggsave("./paper-figures/Figure3-revenue-dotplot.eps") exp_long %>% filter(Year == "2023" | Year == "2022") %>% mutate(Year = as.character(Year)) %>% ggplot(aes(x = Dollars/1000, y = reorder(Category, Dollars))) + geom_line(aes(group = Category) )+ geom_text(aes(x = ifelse(Year == "2023", (as.numeric(Dollars)/1000)+1, NA), label = ifelse(Year == "2023", Category_name, "")), hjust = 0, size = 2.8) + geom_point(aes(color = Year), size=2 #, alpha = 0.5 ) + labs(title = "2022 to 2023 Change in Expenditures", x = "Millions of Dollars" , y = "", caption = "") + scale_fill_manual(values = c("#d62828", "#003049"), labels = c("FY 2022", "FY 2023"))+ scale_color_manual(values = c("#d62828", "#003049")) + theme_classic()+ theme( legend.position = "bottom" , axis.text.y = element_blank(), axis.ticks.y = element_blank(), axis.line.y.left = element_blank() ) + scale_x_continuous(limits = c(0, 35), labels = comma) ``` ### Top 3 Revenues {#sec-top-3-revenues} ```{r} #| label: fig-top3-revenues #| fig-cap: Top 3 Revenue Sources (Own-Source Revenues only) #| fig-cap-location: top annotation <- data.frame( x = c(2013, 2018, 2013), y = c(16, 10, 5), label = c("Individual Income Tax", "Sales Tax", "Corporate Income Tax") ) top3 <- rev_long %>% filter(Category == "02" | Category == "03" | Category == "06") top3 <- ggplot(data = top3, aes(x=Year, y=Dollars/1000))+ geom_recessions(text = FALSE)+ geom_line(aes(x=Year, y=Dollars/1000, color = Category_name)) + geom_text(data = annotation, aes(x=x, y=y, label=label)) + theme_classic() + scale_x_continuous(expand = c(0,0)) + scale_y_continuous(labels = comma) + scale_linetype_manual(values = c("dotted", "dashed", "solid")) + theme(legend.position = "none") + labs(title = "Top 3 Own Source Revenues", subtitle = "Individual Income Taxes, Sales Tax, and Corporate income taxes", y = "Billions of Nominal Dollars") top3 ``` **Sales Tax - online retailers** <div> ::: callout-warning ## Online Retailer Warning Not edited or double checked. Randomly looked into online retailers recently and didn't finish thoughts on it. Just general notes pulled together while looking into online sales tax. ::: </div> Law was passed in 2018 that required out of state retailers to pay the 6.25% **state** sales tax. The Rebuild Illinois law expanded the law to require remote retailers to charge all state and local retailers occupation taxes beginning in July 1, 2020. Before Jan. 1 2021, only state sales taxes were required to be collected (related to South Dakota v Wayfair court decision). Now required to pay state **and local** tax based on where product is delivered. "On June 28, 2019, Public Act 101-0031, the"Leveling the Playing Field for Illinois Retail Act," was signed into Illinois law and on December 13, 2019 an amendment to the Act was signed into law in Public Act 101-0604. In an effort to create more equity between remote sellers and local brick-and-mortar retailers, the new law requires remote sellers without a physical presence in the state and marketplace facilitators (e.g., Amazon and Walmart) to collect both state and local sales taxes effective January 1, 2021." [CivicFed.org](https://www.civicfed.org/civic-federation/blog/consumer-taxes-chicago-increases-and-updates-2021) Requires remote sellers and marketplace facilitators to collect and remit the state and locally-imposed Retailers' Occupation Tax (ROT) for the jurisdictions where the product is delivered (destination sourcing) rather than collecting and remitting solely the state use tax. \ ::: aside Illinois' State sales tax rate is 6.25%, of which 5.0% of the sales tax revenue goes to the State, 1.0% goes to all municipalities, including Chicago, and the remaining 0.25% goes to the counties. However, Cook County's 0.25% share of the State sales tax is distributed to the Regional Transportation Authority. ::: "The amended"Leveling the Playing Field for Illinois Retail Act" was passed by the General Assembly on November 14, 2019, to require both Remote Retailers and Marketplace Facilitators to collect and remit the state and locally-imposed Retailers' Occupation Tax (ROT, aka sales tax) for the jurisdictions where the product is delivered (its destination) starting January 1, 2021."- [Illinois Municipal League](https://www.iml.org/file.cfm?key=16124) - Marketplace Facilitators, like Amazon, were required to collect Use Tax on sales starting January 1, 2020 - Other sellers required to collect state and local sales tax on sales on January 2021. - There is a **state** tax rate of 6.25% and Illinois municipalities may impose an additional **local** sales tax called the Retailer's Occupation Tax. - For remote sellers, the state tax rate is referred to as "use tax" and for intrastate sellers, "ROT" simply means *sales tax*. - The ROT is measured upon the seller's gross receipts and the seller is statutorily [required]{.underline} to collect the use tax from their customers. - source 0482 is State ROT-2.2% [ILGA info](https://www.ilga.gov/legislation/ilcs/ilcs5.asp?ActID=3993&ChapterID=8) - leveling the playing field went into effect on July 1 2020 which is the beginning of FY21 ```{r} #| label: online-retailers ## State Retailers Occupation Tax. rev_temp %>% filter(source == "0481") %>% group_by(fy, source_name_AWM) %>% summarize(revenue=sum(receipts)) rev_temp %>% filter(source == "0481") %>% group_by(fy, source_name_AWM, fund_name_ab) %>% summarize(revenue=sum(receipts))%>% arrange(-fy, -revenue)%>% pivot_wider(names_from = "fy", values_from="revenue") rev_temp %>% filter(source == "0481") %>% #group_by(fy, source_name_AWM, fund_name_ab) %>% # summarize(revenue=sum(receipts)) %>% ggplot(aes(x=fy, y=receipts))+ geom_recessions()+ geom_line(aes(color=fund_name_ab))+ geom_vline(xintercept = 2018)+ geom_vline(xintercept = 2021)+ theme_classic()+ scale_x_continuous(expand = c(0,0)) labs(title="State Retailers' Occupation Tax, Source 0481", caption = "Leveling the Playing Field went into effect for Amazon on January 1, 2020(mid-FY21) and for other remote retailers starting January 1, 2021 (mid-FY22)") ### Remote Occupation Tax # STATE ROT-2.2% rev_temp %>% filter(source == "0482") %>% group_by(fy, source_name_AWM) %>% summarize(revenue=sum(receipts)) rev_temp %>% filter(source == "0482") %>% group_by(fy, source_name_AWM, fund_name_ab) %>% summarize(revenue=sum(receipts))%>% arrange(-fy, -revenue)%>% pivot_wider(names_from = "fy", values_from="revenue") rev_temp %>% filter(source == "0482") %>% #group_by(source_name_AWM) %>% #summarize(revenue=sum(receipts)) %>% ggplot(aes(x=fy, y=receipts))+ geom_line(aes(color=fund_name_ab))+ geom_recessions()+ geom_vline(xintercept = 2018)+ geom_vline(xintercept = 2020)+ theme_classic() + scale_x_continuous(expand = c(0,0)) + labs(title="State Retailers' Occupation Tax", subtitle = "Large increases due to Leveling the Playing Field Act & Online shopping during pandemic") rev_temp %>% filter(source == "0482") %>% group_by(fy, source_name_AWM, fund_name_ab) %>% summarize(revenue=sum(receipts)) %>% ggplot()+ geom_line(aes(x=fy, y=revenue, color=fund_name_ab))+ geom_vline(xintercept = 2018) + geom_vline(xintercept = 2021) + #geom_recessions(aes(x=fy, y=receipts)+ theme_classic()+ scale_x_continuous(expand = c(0,0)) + labs(title="State ROT - 2.2%", subtitle = "Large increases due to Leveling the Playing Field Act & Online shopping during pandemic??", caption = "State tax began being collected for remote retailers based on destination beginning in Leveling the Playing Field went into effect for Amazon on January 1, 2020(mid-FY21) and for other remote retailers starting January 1, 2021 (mid-FY22)") rev_temp %>% filter(source == "0482" | source == "0481") %>% group_by(fy, source_name_AWM) %>% summarize(revenue=sum(receipts)) %>% ggplot()+ geom_line(aes(x=fy, y=revenue, color=source_name_AWM))+ geom_vline(xintercept = 2018)+ geom_vline(xintercept = 2021)+ #geom_recessions(aes(x=fy, y=receipts)+ theme_classic()+ scale_x_continuous(expand = c(0,0)) + labs(title="State ROT - 2.2% & ", subtitle = "Large increases due to Leveling the Playing Field Act & Online shopping during pandemic??", caption = "Leveling the Playing Field went into effect for Amazon on January 1, 2020(mid-FY21) and for other remote retailers starting January 1, 2021 (mid-FY22)") ``` As of Feb. 6 2023, Source 481 Retailers Occupation Tax has collected \$9.3 billion already. FY22 had \$14.7 million. Around half goes to the General Revenue Fund. ### Own Source and Fed Transfers {#sec-own-source-and-fed-transfers} ```{r} #| label: fig-ownsource-and-fed-revenue #| fig-cap: Comparison of Own Source and Federal Revenue. Historicaly, federal revenue tends to increase when state revenue decreases from some sort of economic shock (e.g. Housing Bubble in 2008). ownsource_rev <- rev_long %>% filter(!Category %in% c("57", "58", "59")) %>% group_by(Year) %>% summarize(Dollars = sum(Dollars)) # ownsource_rev %>% # ggplot()+geom_line(aes(x=Year, y=Dollars)) + # labs(title = "Own Source Revenues", subtitle = "Total own source revenue", y = "Millions of Dollars") fed_rev <- ff_rev %>% select(fy, rev_57, rev_58, rev_59) %>% mutate(fed_total = rev_57+rev_58+rev_59) annotation <- data.frame( x = c(2014, 2015), y = c(50, 25), label = c("Own Source Revenue", "Federal Revenue") ) ownsource_graph <- ggplot(ownsource_rev, aes(x=Year, y=Dollars/1000)) + geom_recessions( text = FALSE)+ geom_line(data = ownsource_rev, aes(x=Year, y=Dollars/1000), color = "Red") + geom_line(data = fed_rev, aes(x=fy, y=fed_total/1000), color = "Black") + geom_text(data = annotation, aes(x=x, y=y, label=label))+ scale_y_continuous(labels = comma)+ scale_x_continuous(expand = c(0,0)) + theme(legend.position = "none")+ theme_classic()+ labs(title = "Own Source Revenue and Federal Revenue", y = "Billions of Nominal Dollars") ownsource_graph ggsave(plot = ownsource_graph, file = "Figure4.eps") ``` # Change from Previous Year Each year, you will need to update the CAGR formulas! Change the filter() year. `calc_cagr` is a function created for calculating the CAGRs for different spans of time. ```{r} exp_totals <- ff_exp %>% rowwise() %>% mutate(exp_TOTALS = sum(across(exp_402:exp_970))) rev_totals <- ff_rev %>% rowwise() %>% mutate(rev_TOTALS = sum(across(rev_02:rev_78))) rev_long <- pivot_longer(rev_totals, rev_02:rev_TOTALS, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>% rename(Year = fy) %>% mutate(Category_name = case_when( Category == "02" ~ "INDIVIDUAL INCOME TAXES" , Category == "03" ~ "CORPORATE INCOME TAXES" , Category == "06" ~ "SALES TAXES" , Category == "09" ~ "MOTOR FUEL TAX" , Category == "12" ~ "PUBLIC UTILITY TAXES" , Category == "15" ~ "CIGARETTE TAXES" , Category == "18" ~ "LIQUOR GALLONAGE TAXES" , Category == "21" ~ "INHERITANCE TAX" , Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES" , Category == "27" ~ "CORP FRANCHISE TAXES & FEES" , Category == "30" ~ "HORSE RACING TAXES & FEES", # in Other Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" , Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped Category == "33" ~ "LOTTERY RECEIPTS" , Category == "35" ~ "OTHER TAXES" , Category == "36" ~ "RECEIPTS FROM REVENUE PRODUCING", Category == "39" ~ "LICENSES, FEES & REGISTRATIONS" , Category == "42" ~ "MOTOR VEHICLE AND OPERATORS" , Category == "45" ~ "STUDENT FEES-UNIVERSITIES", # dropped Category == "48" ~ "RIVERBOAT WAGERING TAXES" , Category == "51" ~ "RETIREMENT CONTRIBUTIONS" , # dropped Category == "54" ~ "GIFTS AND BEQUESTS", Category == "57" ~ "FEDERAL OTHER" , Category == "58" ~ "FEDERAL MEDICAID", Category == "59" ~ "FEDERAL TRANSPORTATION" , Category == "60" ~ "OTHER GRANTS AND CONTRACTS", #other Category == "63" ~ "INVESTMENT INCOME", # other Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped Category == "72" ~ "BOND ISSUE PROCEEDS", #dropped Category == "75" ~ "INTER-AGENCY RECEIPTS ", #dropped Category == "76" ~ "TRANSFER IN FROM OUT FUNDS", #other Category == "78" ~ "ALL OTHER SOURCES" , Category == "79" ~ "COOK COUNTY IGT", #dropped Category == "98" ~ "PRIOR YEAR REFUNDS", #dropped Category == "TOTALS" ~ "Total", T ~ "CHECK ME" ) ) %>% select(-type, -Category) %>% # drop extra columns type and Category number group_by(Year, Category_name) %>% summarise(Dollars= round(sum(Dollars),digits=2)) %>% mutate(Category_name = str_to_title(Category_name)) # creates wide version of table where each revenue source is a column revenue_wide2 <- rev_long %>% pivot_wider(names_from = Category_name, values_from = Dollars) %>% # relocate("Other Revenue Sources **", .after = last_col()) %>% relocate("Total", .after = last_col()) exp_long <- pivot_longer(exp_totals, exp_402:exp_TOTALS , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>% rename(Year = fy ) %>% mutate(Category_name = case_when( Category == "402" ~ "AGING" , Category == "406" ~ "AGRICULTURE", Category == "416" ~ "Central Management", Category == "418" ~ "CHILDREN AND FAMILY SERVICES", Category == "420" ~ "Community Development", Category == "422" ~ "NATURAL RESOURCES" , Category == "426" ~ "CORRECTIONS", Category == "427" ~ "EMPLOYMENT SECURITY" , Category == "444" ~ "Human Services" , Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", Category == "482" ~ "PUBLIC HEALTH", Category == "492" ~ "REVENUE", Category == "494" ~ "Transportation" , Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" , Category == "557" ~ "Tollway" , Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", Category == "691" ~ "IL STUDENT ASSISTANCE COMM" , Category == "900" ~ "NOT IN FRAME", Category == "901" ~ "State Pension Contribution", Category == "903" ~ "Debt Service", Category == "904" ~ "State Employee Healthcare", Category == "910" ~ "LEGISLATIVE" , Category == "920" ~ "JUDICIAL" , Category == "930" ~ "ELECTED OFFICERS" , Category == "940" ~ "OTHER HEALTH-RELATED", Category == "941" ~ "Public Safety" , Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" , Category == "943" ~ "CENTRAL SERVICES", Category == "944" ~ "BUS & PROFESSION REGULATION" , Category == "945" ~ "Medicaid" , Category == "946" ~ "Capital Improvement" , Category == "948" ~ "OTHER DEPARTMENTS" , Category == "949" ~ "OTHER BOARDS & COMMISSIONS" , Category == "959" ~ "K-12 Education" , Category == "960" ~ "UNIVERSITY EDUCATION", Category == "970" ~ "Local Govt Revenue Sharing", Category == "TOTALS" ~ "Total", T ~ "CHECK ME" # T ~ "All Other Expenditures **") )) %>% select(-type, -Category) %>% group_by(Year, Category_name) %>% summarise(Dollars= round(sum(Dollars),digits=2)) %>% mutate(Category_name = str_to_title(Category_name)) expenditure_wide2 <- exp_long%>% pivot_wider(names_from = Category_name, values_from = Dollars) %>% relocate("Total", .after = last_col()) # function for calculating the CAGR calc_cagr <- function(df, n) { df <- exp_long %>% #select(-type) %>% arrange(Category_name, Year) %>% group_by(Category_name) %>% mutate(cagr = ((`Dollars` / lag(`Dollars`, n)) ^ (1 / n)) - 1) return(df) } ``` Things to do when updating the code: - Each year, you need to increase the cagr value by 1. The value should be the (current year - 1998). For FY23, this is 2023-1998 = 25. So all cagr values that were 24 will be changed to 25. ```{r } #| label: tbl-good-exp-CAGR-withTotals #| tbl-cap: Expenditure Category CAGRs with Total CAGR (Ordered Alphabetically) #| tbl-cap-location: top # This works for one variable at a time cagr_25 <- calc_cagr(exp_long, 25) %>% # group_by(Category) %>% summarize(cagr_25 = round(sum(cagr*100, na.rm = TRUE), 2)) # doesn't need to be changed since it is just pre-covid cagr_precovid <- exp_long %>% filter(Year <= 2019) %>% calc_cagr(21) %>% summarize(cagr_21 = round(sum(cagr*100, na.rm = TRUE), 2)) # Update year in the filter() and summarize() commands to current year. cagr_10 <- calc_cagr(exp_long, 10) %>% filter(Year == 2023) %>% summarize(cagr_10 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_5 <- calc_cagr(exp_long, 5) %>% filter(Year == 2023) %>% summarize(cagr_5 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_3 <- calc_cagr(exp_long, 3) %>% filter(Year == 2023) %>% summarize(cagr_3 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_2 <- calc_cagr(exp_long, 2) %>% filter(Year == 2023) %>% summarize(cagr_2 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_1 <- calc_cagr(exp_long, 1) %>% filter(Year == 2023) %>% summarize(cagr_1 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) # update variables so cagr_24 becomes cagr_25 CAGR_expenditures_summary_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_25 ) %>% select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>% rename("Expenditure Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10,"25 Year CAGR" = cagr_25 ) move_to_last <- function(df, n) df[c(setdiff(seq_len(nrow(df)), n), n), ] CAGR_expenditures_summary_tot <- move_to_last(CAGR_expenditures_summary_tot, 29 ) #CAGR_expenditures_summary_tot <- select(CAGR_expenditures_summary_tot, -1) CAGR_expenditures_summary_tot %>% kbl(caption = "CAGR Calculations for All Expenditure Categories" , row.names=FALSE) %>% kable_classic() %>% row_spec(31, bold = T, color = "black", background = "gray") ``` ```{r } #| label: tbl-RevCAGRs-allcats #| tbl-cap: Revenue Category CAGRs with Total CAGR (Ordered Alphabetically) #| tbl-cap-location: top # revenue version function: calc_cagr <- function(df, n) { df <- rev_long %>% arrange(Category_name, Year) %>% group_by(Category_name) %>% mutate(cagr = ((Dollars / lag(Dollars, n)) ^ (1 / n)) - 1) return(df) } # This works for one variable at a time cagr_25 <- calc_cagr(rev_long, 25) %>% # group_by(Category) %>% summarize(cagr_25 = round(sum(cagr*100, na.rm = TRUE), 2)) cagr_10 <- calc_cagr(rev_long, 10) %>% filter(Year == 2023) %>% summarize(cagr_10 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_5 <- calc_cagr(rev_long, 5) %>% filter(Year == 2023) %>% summarize(cagr_5 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_3 <- calc_cagr(rev_long, 3) %>% filter(Year == 2023) %>% summarize(cagr_3 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_2 <- calc_cagr(rev_long, 2) %>% filter(Year == 2023) %>% summarize(cagr_2 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_1 <- calc_cagr(rev_long, 1) %>% filter(Year == 2023) %>% summarize(cagr_1 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) CAGR_revenue_summary_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_25) %>% select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>% rename("Revenue Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10,"25 Year CAGR" = cagr_25 ) CAGR_revenue_summary_tot <- move_to_last(CAGR_revenue_summary_tot,1) CAGR_revenue_summary_tot <- move_to_last(CAGR_revenue_summary_tot,22) CAGR_revenue_summary_tot %>% kbl(caption = "CAGR Calculations for All Revenue Sources (Ordered Alphabetical)", row.names = FALSE) %>% kable_classic() %>% row_spec(23, bold = T, color = "black", background = "gray") ``` ```{r include = FALSE} rm(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_25) ``` Update all years in mutate() commands so that they all go up by 1: ```{r } revenue_change2 <- rev_long %>% #select(-c(Category)) %>% filter(Year > 2021) %>% pivot_wider(names_from = Year , values_from = Dollars, names_prefix = "Dollars_") %>% mutate( "FY 2023 ($ billions)" = round(Dollars_2023/1000, digits = 1), "FY 2022 ($ billions)" = round(Dollars_2022/1000, digits = 1), # "Change from 2022 to 2023" = round(Dollars_2022 - Dollars_2021, digits = 2), "1-Year Change" = round(((Dollars_2023 -Dollars_2022)/Dollars_2022*100), digits = 2)) %>% left_join(CAGR_revenue_summary_tot, by = c("Category_name" = "Revenue Category")) %>% arrange(-`FY 2023 ($ billions)`)%>% # filter(Category_ame != "NA") %>% #select(-c(Dollars_2021, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>% rename( "25-Year CAGR" = `25 Year CAGR`, "Revenue Category" = Category_name ) %>% select(-c(Dollars_2022, Dollars_2023, `1 Year CAGR`:`10 Year CAGR`)) revenue_change2 <- move_to_last(revenue_change2,8) revenue_change2 <- move_to_last(revenue_change2,1) revenue_change2 %>% filter(!is.na(`Revenue Category`)) %>% kbl(caption = "Table 1. Yearly Change in Revenue", row.names = FALSE) %>% kable_classic() %>% row_spec(23, bold = T, color = "black", background = "gray") expenditure_change2 <- exp_long %>% #select(-c(type,Category)) %>% filter(Year > 2021) %>% pivot_wider(names_from = Year , values_from = Dollars, names_prefix = "Dollars_") %>% mutate( "FY 2023 ($ billions)" = round(Dollars_2023/1000, digits = 1), "FY 2022 ($ billions)" = round(Dollars_2022/1000, digits = 1), # "Change from 2021 to 2022" = Dollars_2022 - Dollars_2021, "1-Year Change" = round((Dollars_2023 -Dollars_2022)/Dollars_2022*100, digits = 2) )%>% left_join(CAGR_expenditures_summary_tot, by = c("Category_name" = "Expenditure Category")) %>% arrange(-`FY 2023 ($ billions)`)%>% select(-c(Dollars_2023, Dollars_2022, `1 Year CAGR`:`10 Year CAGR`)) %>% rename( "25-Year CAGR" = `25 Year CAGR`, "Expenditure Category" = Category_name ) expenditure_change2 <- move_to_last(expenditure_change2, 1) expenditure_change2 %>% filter(!is.na(`Expenditure Category`)) %>% kbl(caption = "Table 2. Yearly Change in Expenditures - All FF Categories, Ordered from Largest to Smallest Expenditure Amount", row.names = FALSE) %>% kable_classic() %>% row_spec(31, bold = T, color = "black", background = "gray") ``` ## Summary Tables - Largest Categories The 10 largest revenue sources and 15 largest expenditure sources remain separate categories and all other smaller sources/expenditures are combined into "All Other Revenues (Expenditures)". These condensed tables are typically used in the Fiscal Futures articles. They were manually created in past years but this hopefully automates the process a bit until final formatting stages. - take ff_rev and ff_exp data frames, which were in wide format, pivot them longer and mutate the Category_name variable to nicer labels. Keep largest categories separate and aggregate the rest. - You need to manually comment out the categories that are not the largest each year. Check and compare to the previous years largest categories! ```{r } #| label: tbl-top-exp-CAGRs #| tbl-cap: Largest Expenditure Categories with CAGRs exp_totals <- ff_exp %>% rowwise() %>% mutate(exp_TOTALS = sum(across(exp_402:exp_970))) # creates total column too rev_totals <- ff_rev %>% rowwise() %>% mutate(rev_TOTALS = sum(across(rev_02:rev_78))) rev_long_majorcats <- pivot_longer(rev_totals, rev_02:rev_TOTALS, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>% rename(Year = fy) %>% mutate(Category_name = case_when( Category == "02" ~ "Income Tax" , Category == "03" ~ "Corporate Income Tax" , Category == "06" ~ "Sales Tax" , Category == "09" ~ "Motor Fuel Taxes" , # Category == "12" ~ "PUBLIC UTILITY TAXES, gross of PPRT" , # Category == "15" ~ "CIGARETTE TAXES" , # Category == "18" ~ "LIQUOR GALLONAGE TAXES" , # Category == "21" ~ "INHERITANCE TAX" , # Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES, net of refunds " , # Category == "27" ~ "CORP FRANCHISE TAXES & FEES" , # Category == "30" ~ "HORSE RACING TAXES & FEES", # in Other Category == "31" ~ "Medical Provider Assessments" , # Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped # Category == "33" ~ "LOTTERY RECEIPTS" , # Category == "35" ~ "OTHER TAXES" , Category == "36" ~ "Receipts from Revenue Producing", Category == "39" ~ "Licenses, Fees, Registration" , # Category == "42" ~ "MOTOR VEHICLE AND OPERATORS" , # Category == "45" ~ "STUDENT FEES-UNIVERSITIES", # dropped # Category == "48" ~ "RIVERBOAT WAGERING TAXES" , # Category == "51" ~ "RETIREMENT CONTRIBUTIONS" , # dropped # Category == "54" ~ "GIFTS AND BEQUESTS", Category == "57" ~ "Federal Other" , Category == "58" ~ "Federal Medicaid Reimbursements", Category == "59" ~ "Federal Transportation" , # Category == "60" ~ "OTHER GRANTS AND CONTRACTS", #other # Category == "63" ~ "INVESTMENT INCOME", # other # Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped # Category == "72" ~ "BOND ISSUE PROCEEDS", #dropped # Category == "75" ~ "INTER-AGENCY RECEIPTS ", #dropped # Category == "76" ~ "TRANSFER IN FROM OUT FUNDS", #other # Category == "78new" ~ "ALL OTHER SOURCES" , # Category == "79" ~ "COOK COUNTY IGT", #dropped # Category == "98" ~ "PRIOR YEAR REFUNDS", #dropped Category == "TOTALS" ~ "Total Revenue", T ~ "All Other Sources **" # any other Category number that was not specifically referenced is combined into Other Revenue Sources ) ) %>% select(-type, -Category) %>% # drop extra columns type and Category number group_by(Year, Category_name) %>% summarise(Dollars= round(sum(Dollars),digits=2)) # revenue_wide # not actually in wide format yet. # has 10 largest rev sources separate and combined all others to Other in long data format. # creates wide version of table where each revenue source is a column revenue_wide_majorcats <- rev_long_majorcats %>% pivot_wider(names_from = Category_name, values_from = Dollars) %>% relocate("All Other Sources **", .after = last_col()) %>% relocate("Total Revenue", .after = last_col()) exp_long_majorcats <- pivot_longer(exp_totals, exp_402:exp_TOTALS , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>% rename(Year = fy ) %>% mutate(Category_name = case_when( # Category == "402" ~ "Aging" , # Category == "406" ~ "AGRICULTURE", # Category == "416" ~ "Central Management", Category == "418" ~ "Children & Family Services", Category == "420" ~ "Community Development", # Category == "422" ~ "NATURAL RESOURCES" , Category == "426" ~ "Corrections", # Category == "427" ~ "EMPLOYMENT SECURITY" , Category == "444" ~ "Human Services" , # Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", # Category == "482" ~ "PUBLIC HEALTH", Category == "492" ~ "Revenue", Category == "494" ~ "Transportation" , # Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" , Category == "557" ~ "Tollway" , # Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", # Category == "691" ~ "IL STUDENT ASSISTANCE COMM" , # Category == "900" ~ "NOT IN FRAME", Category == "901" ~ "State Pension Contribution", Category == "903" ~ "Debt Service", Category == "904" ~ "State Employee Healthcare", # Category == "910" ~ "LEGISLATIVE" , # Category == "920" ~ "JUDICIAL" , # Category == "930" ~ "Elected Officers" , # Category == "940" ~ "OTHER HEALTH-RELATED", Category == "941" ~ "Public Safety" , # Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" , # Category == "943" ~ "CENTRAL SERVICES", # Category == "944" ~ "BUS & PROFESSION REGULATION" , Category == "945" ~ "Medicaid" , # Category == "946" ~ "Capital Improvement" , # Category == "948" ~ "OTHER DEPARTMENTS" , # Category == "949" ~ "OTHER BOARDS & COMMISSIONS" , Category == "959" ~ "K-12 Education" , Category == "960" ~ "University Education", Category == "970" ~ "Local Govt Revenue Sharing", Category == "TOTALS" ~ "Total Expenditures", T ~ "All Other Expenditures **") ) %>% select(-type, -Category) %>% group_by(Year, Category_name) %>% summarise(Dollars= round(sum(Dollars),digits=2)) expenditure_wide_majorcats <- exp_long_majorcats %>% pivot_wider(names_from = Category_name, values_from = Dollars) %>% relocate("All Other Expenditures **", .after = last_col()) %>% relocate("Total Expenditures", .after = last_col()) # CAGR values for largest expenditure categories and combined All Other Expenditures # function for calculating the CAGR calc_cagr <- function(df, n) { df <- exp_long_majorcats %>% #select(-type) %>% arrange(Category_name, Year) %>% group_by(Category_name) %>% mutate(cagr = ((`Dollars` / lag(`Dollars`, n)) ^ (1 / n)) - 1) return(df) } # This works for one variable at a time cagr_25 <- calc_cagr(exp_long_majorcats, 25) %>% # group_by(Category) %>% summarize(cagr_25 = round(sum(cagr*100, na.rm = TRUE), 2)) cagr23_precovid <- exp_long_majorcats %>% filter(Year <= 2019) %>% calc_cagr(21) %>% summarize(cagr_21 = round(sum(cagr*100, na.rm = TRUE), 2)) cagr_10 <- calc_cagr(exp_long_majorcats, 10) %>% filter(Year == 2023) %>% summarize(cagr_10 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_5 <- calc_cagr(exp_long_majorcats, 5) %>% filter(Year == 2023) %>% summarize(cagr_5 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_3 <- calc_cagr(exp_long_majorcats, 3) %>% filter(Year == 2023) %>% summarize(cagr_3 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_2 <- calc_cagr(exp_long_majorcats, 2) %>% filter(Year == 2023) %>% summarize(cagr_2 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_1 <- calc_cagr(exp_long_majorcats, 1) %>% filter(Year == 2023) %>% summarize(cagr_1 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) CAGR_expenditures_majorcats_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_25 ) %>% select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>% rename("Expenditure Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10,"25-Year CAGR" = cagr_25 ) move_to_last <- function(df, n) df[c(setdiff(seq_len(nrow(df)), n), n), ] CAGR_expenditures_majorcats_tot <- move_to_last(CAGR_expenditures_majorcats_tot, 1) CAGR_expenditures_majorcats_tot <- move_to_last(CAGR_expenditures_majorcats_tot, 14) CAGR_expenditures_majorcats_tot%>% kbl(caption = "CAGR Calculations for Largest Expenditure Categories" , row.names=FALSE) %>% kable_classic() %>% row_spec(17, bold = T, color = "black", background = "gray") current_year <- 2023 last_year <- 2022 # Yearly change for Top 13 largest expenditure categories expenditure_change_majorcats <- exp_long_majorcats %>% filter(Year > 2021) %>% pivot_wider(names_from = Year , values_from = Dollars, names_prefix = "Dollars_") %>% mutate("FY 2023 ($ Billions)" = round(Dollars_2023/1000, digits = 1), "FY 2022 ($ Billions)" = round(Dollars_2022/1000, digits = 1), "1-Year Change" = percent((Dollars_2023 -Dollars_2022)/Dollars_2022, accuracy = .1) ) %>% left_join(CAGR_expenditures_majorcats_tot, by = c("Category_name" = "Expenditure Category")) %>% arrange(-`FY 2023 ($ Billions)`)%>% mutate(`25-Year CAGR` = percent(`25-Year CAGR`/100, accuracy=.1)) %>% select(-c(Dollars_2023, Dollars_2022, `1 Year CAGR`:`10 Year CAGR`)) %>% rename( "25-Year CAGR" = `25-Year CAGR`, "Expenditure Category" = Category_name ) expenditure_change_majorcats <- move_to_last(expenditure_change_majorcats, 4) expenditure_change_majorcats <- move_to_last(expenditure_change_majorcats, 1) expenditure_change_majorcats %>% kbl(caption = "Yearly Change in Expenditures", row.names = FALSE, align = "l") %>% kable_classic() %>% row_spec(17, bold = T, color = "black", background = "gray") ``` Top 10 revenue sources CAGRs and Yearly Change Tables: ```{r} #| label: tbl-top10-rev-CAGRs #| tbl-cap: Top 10 Revenue Sources with CAGRs #| tbl-cap-location: top ##### Top 10 revenue CAGRs: #### calc_cagr <- function(df, n) { df <- rev_long_majorcats %>% arrange(Category_name, Year) %>% group_by(Category_name) %>% mutate(cagr = ((Dollars / lag(Dollars, n)) ^ (1 / n)) - 1) return(df) } # This works for one variable at a time cagr_25 <- calc_cagr(rev_long_majorcats, 25) %>% # group_by(Category) %>% summarize(cagr_25 = round(sum(cagr*100, na.rm = TRUE), 2)) cagr_10 <- calc_cagr(rev_long_majorcats, 10) %>% filter(Year == 2023) %>% summarize(cagr_10 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_5 <- calc_cagr(rev_long_majorcats, 5) %>% filter(Year == 2023) %>% summarize(cagr_5 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_3 <- calc_cagr(rev_long_majorcats, 3) %>% filter(Year == 2023) %>% summarize(cagr_3 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_2 <- calc_cagr(rev_long_majorcats, 2) %>% filter(Year == 2023) %>% summarize(cagr_2 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) cagr_1 <- calc_cagr(rev_long_majorcats, 1) %>% filter(Year == 2023) %>% summarize(cagr_1 = case_when(Year == 2023 ~ round(sum(cagr*100, na.rm = TRUE), 2))) CAGR_revenue_majorcats_tot <- data.frame(cagr_1, cagr_2, cagr_3, cagr_5, cagr_10, cagr_25) %>% select(-c(Category_name.1, Category_name.2, Category_name.3, Category_name.4, Category_name.5 )) %>% rename("Revenue Category" = Category_name, "1 Year CAGR" = cagr_1, "2 Year CAGR" = cagr_2, "3 Year CAGR" = cagr_3, "5 Year CAGR" = cagr_5, "10 Year CAGR" = cagr_10, "25-Year CAGR" = cagr_25 ) CAGR_revenue_majorcats_tot <- move_to_last(CAGR_revenue_majorcats_tot,1) CAGR_revenue_majorcats_tot <- move_to_last(CAGR_revenue_majorcats_tot,11) CAGR_revenue_majorcats_tot %>% kbl(caption = "CAGR Calculations for Largest Revenue Sources", row.names = FALSE) %>% kable_classic() %>% row_spec(12, bold = T, color = "black", background = "gray") ``` ```{r } #| label: tbl-top10-rev-yearlychange #| tbl-cap: Top 10 Revenue Sources with CAGRs #| tbl-cap-location: top ###### Yearly change summary table for Top 10 Revenues ##### revenue_change_majorcats <- rev_long_majorcats %>% #select(-c(Category)) %>% filter(Year > 2021) %>% pivot_wider(names_from = Year , values_from = Dollars, names_prefix = "Dollars_") %>% mutate( "FY 2023 ($ billions)" = round(Dollars_2023/1000, digits = 1), "FY 2022 ($ billions)" = round(Dollars_2022/1000, digits = 1), "1-Year Change" = percent(((Dollars_2023 -Dollars_2022)/Dollars_2022), accuracy = .1)) %>% left_join(CAGR_revenue_majorcats_tot, by = c("Category_name" = "Revenue Category")) %>% arrange(-`FY 2023 ($ billions)`)%>% #select(-c(Dollars_2021, Dollars_2021, `1 Year CAGR`:`10 Year CAGR`)) %>% mutate("25-Year Change" = percent(`25-Year CAGR`/100, accuracy=.1)) %>% rename("Revenue Category" = Category_name ) %>% select(-c(Dollars_2023, Dollars_2022, `1 Year CAGR`:`25-Year CAGR`)) revenue_change_majorcats <- move_to_last(revenue_change_majorcats,5) revenue_change_majorcats <- move_to_last(revenue_change_majorcats,1) revenue_change_majorcats%>% kbl(caption = "Yearly Change in Revenue for Main Revenue Sources", row.names = FALSE, align = "l") %>% kable_classic() %>% row_spec(12, bold = T, color = "black", background = "gray") ``` Export summary file with Totals ```{r} #install.packages("openxlsx") library(openxlsx) dataset_names <- list('Aggregate Revenues' = revenue_wide2, 'Aggregate Expenditures' = expenditure_wide2, 'Table 1' = revenue_change_majorcats, #Top categories with yearly change, 23 yr cagr 'Table 2' = expenditure_change_majorcats, 'Table 1a. AllCats' = revenue_change2, 'Table 2a. AllCats' = expenditure_change2, 'CAGR Rev-MajorCats' = CAGR_revenue_majorcats_tot, # Categories Match Table 1 in paper 'CAGR Exp-MajorCats' = CAGR_expenditures_majorcats_tot, # 'Table 1-AllCats' = expenditure_change_allcats, # All Categories by Year # 'Table 2-AllCats' = revenue_change_allcats, # 'CAGR_Revenue-AllCats' = CAGR_revenue_summary_tot, # 'CAGR_Expenditures-AllCats' = CAGR_expenditures_summary_tot, 'Fiscal Gap' = year_totals, # Total Revenue, Expenditure, and Fiscal gap per year 'aggregated_totals_long' = aggregated_totals_long # all data in long format. Good for creating pivot tables in Excel ) write.xlsx(dataset_names, file = 'summary_file_FY23_wTotals_Dec292023.xlsx') ``` Saves main items in one excel file named `summary_file.xlsx`. Delete `eval=FALSE` to run on local computer. # Drop COVID Dollars If only sustainable revenues are included in the model, then the federal dollars from the pandemic response (CARES, CRSSA,& ARPA) should be excluded from the calculation of the fiscal gap. The Fiscal Futures model focuses on sustainable revenue sources. To understand our fiscal gap and outlook, we need to exclude these one time revenues. GOMB has emphasized that they have allocated COVID dollars to one time expenditures (unemployment trust fund, budget stabilization fund, etc.). The fiscal gap, graphs,and CAGRs have been recalculated in the [Drop COVID Dollars] section below. > NOTE: I have only dropped revenue with a source name = `Federal Stimulus Package`. Federal money went into other funds during the beginning of pandemic. All additional money for medicaid reimbursements and healthcare provider funds were not considered "Federal Stimulus Package" in the data and were not dropped. - fund 0628 is essential government support services. Money in the fund is appropriated to cover COVID-19 related expenses. It should be included in our analytical frame based on criteria 2 and6 --- the fund supports an important state function about public safety, which would have to be performed even the fund structure were not existed. Public safety is supported by a combination of departments and boards, including IL Emergency Management Agency, which is the administering agency of the fund. - Education Stabilization Fund\ - ESSER 1, 2, and 3\ - CSLFRF (State and Local CURE)\ - Provider Relief Fund\ - Coronavirus Relief Fund (CRF)\ - Consolidated Appropriations Act 2020\ - Families First Coronavirus Response Act\ - Paycheck Protection Program and Health Care Enhancement Act Need to recreate ff_exp and ff_rev totals without stimulus dollars. ```{r} rev_temp <- rev_temp %>% mutate(covid_dollars = ifelse(source_name_AWM == "FEDERAL STIMULUS PACKAGE",1,0)) rev_temp %>% filter(source_name_AWM == "FEDERAL STIMULUS PACKAGE") %>% group_by(fy) %>% summarize(Received = sum(receipts)) ``` ```{r drop-coviddollars} # does not include rev_type == 58, medicaid dollars covid_dollars_rev <- rev_temp %>% filter(covid_dollars==1) # check what was dropped covid_dollars_rev %>% group_by(fy, agency_name) %>% summarize(receipts = sum(receipts)) %>% pivot_wider(names_from="agency_name", values_from = "receipts") %>% arrange(-fy) covid_dollars_rev %>% group_by(fy, fund_name_ab) %>% summarize(receipts = sum(receipts)) %>% arrange(-fy) %>% pivot_wider(names_from="fy", values_from = "receipts") K12_ESSER_words <- c("CRRSA","ESSER","EMER R", "EMR R", "CARES", "AMER R", "EMER ED") ESSER_exp <- exp_temp %>% filter(agency_name == "STATE BOARD OF EDUCATION") %>% mutate(ESSERfunds = case_when( str_detect(wh_approp_name, "CRRSA") ~ "ESSER", str_detect(wh_approp_name, "ESSER") ~ "ESSER", str_detect(wh_approp_name, "EMER R") ~ "ESSER", str_detect(wh_approp_name, "EMR R") ~ "ESSER", str_detect(wh_approp_name, "CARES") ~ "ESSER", str_detect(wh_approp_name, "AMER R") ~ "ESSER", str_detect(wh_approp_name, "EMER ED") ~ "ESSER", TRUE ~ 'not_esser')) %>% filter(ESSERfunds == "ESSER") ESSER_exp ``` ```{r drop-coviddollars2} rev_temp <- rev_temp %>% filter(covid_dollars==0) # keeps observations that were not coded as COVID federal funds ff_rev <- rev_temp %>% group_by(rev_type_new, fy) %>% summarize(sum_receipts = round(sum(receipts, na.rm=TRUE)/1000000 )) %>% pivot_wider(names_from = "rev_type_new", values_from = "sum_receipts", names_prefix = "rev_") ff_rev<- left_join(ff_rev, tax_refund) ff_rev <- mutate_all(ff_rev, ~replace_na(.,0)) ff_rev <- ff_rev %>% mutate(rev_02 = rev_02 - ref_02, rev_03 = rev_03 - ref_03, rev_06 = rev_06 - ref_06, rev_09 = rev_09 - ref_09, rev_21 = rev_21 - ref_21, rev_24 = rev_24 - ref_24, rev_35 = rev_35 - ref_35 ) %>% select(-c(ref_02:ref_35, rev_99, rev_76, ref_CHECK )) rev_long <- pivot_longer(ff_rev, rev_02:rev_78, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>% rename(Year = fy) %>% mutate(Category_name = case_when( Category == "02" ~ "INDIVIDUAL INCOME TAX" , Category == "03" ~ "CORPORATE INCOME TAX" , Category == "06" ~ "SALES TAX" , Category == "09" ~ "MOTOR FUEL TAX" , Category == "12" ~ "PUBLIC UTILITY TAX" , Category == "15" ~ "CIGARETTE TAXES" , Category == "18" ~ "LIQUOR GALLONAGE TAXES" , Category == "21" ~ "INHERITANCE TAX" , Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES" , Category == "27" ~ "CORP FRANCHISE TAXES & FEES" , Category == "30" ~ "HORSE RACING TAXES & FEES", # in Other Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" , Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped Category == "33" ~ "LOTTERY RECEIPTS" , Category == "35" ~ "OTHER TAXES" , Category == "36" ~ "RECEIPTS FROM REVENUE PRODUCING", Category == "39" ~ "LICENSES, FEES & REGISTRATIONS" , Category == "42" ~ "MOTOR VEHICLE AND OPERATORS" , Category == "45" ~ "STUDENT FEES-UNIVERSITIES", # dropped Category == "48" ~ "RIVERBOAT WAGERING TAXES" , Category == "51" ~ "RETIREMENT CONTRIBUTIONS" , # dropped Category == "54" ~ "GIFTS AND BEQUESTS", Category == "57" ~ "FEDERAL OTHER" , Category == "58" ~ "FEDERAL MEDICAID", Category == "59" ~ "FEDERAL TRANSPORTATION" , Category == "60" ~ "OTHER GRANTS AND CONTRACTS", #other Category == "63" ~ "INVESTMENT INCOME", # other Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped Category == "72" ~ "BOND ISSUE PROCEEDS", #dropped Category == "75" ~ "INTER-AGENCY RECEIPTS ", #dropped Category == "76" ~ "TRANSFER IN FROM OUT FUNDS", #other Category == "78" ~ "ALL OTHER SOURCES" , Category == "79" ~ "COOK COUNTY IGT", #dropped Category == "98" ~ "PRIOR YEAR REFUNDS", #dropped T ~ "Check Me!" ) )%>% mutate(Category_name = str_to_title(Category_name)) ff_exp <- exp_temp %>% group_by(fy, group) %>% summarize(sum_expenditures = round(sum(expenditure, na.rm=TRUE)/1000000 )) %>% pivot_wider(names_from = "group", values_from = "sum_expenditures", names_prefix = "exp_")%>% left_join(debt_keep_yearly) %>% mutate(exp_903 = debt_cost) %>% left_join(transfers) %>% mutate(exp_970 = exp_971 + exp_972 + exp_975 + exp_976) ff_exp<- ff_exp %>% select(-c(debt_cost, exp_971:exp_976)) # drop unwanted columns exp_long <- pivot_longer(ff_exp, exp_402:exp_970 , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>% rename(Year = fy ) %>% mutate(Category_name = case_when( Category == "402" ~ "AGING" , Category == "406" ~ "AGRICULTURE", Category == "416" ~ "CENTRAL MANAGEMENT", Category == "418" ~ "CHILDREN AND FAMILY SERVICES", Category == "420" ~ "COMMERCE AND ECONOMIC OPPORTUNITY", Category == "422" ~ "NATURAL RESOURCES" , Category == "426" ~ "CORRECTIONS", Category == "427" ~ "EMPLOYMENT SECURITY" , Category == "444" ~ "HUMAN SERVICES" , Category == "448" ~ "Innovation and Technology", # AWM added fy2022 Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", Category == "482" ~ "PUBLIC HEALTH", Category == "492" ~ "REVENUE", Category == "494" ~ "TRANSPORTATION" , Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" , Category == "557" ~ "IL STATE TOLL HIGHWAY AUTH" , Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", Category == "691" ~ "IL STUDENT ASSISTANCE COMM" , Category == "900" ~ "NOT IN FRAME", Category == "901" ~ "STATE PENSION CONTRIBUTION", Category == "903" ~ "DEBT SERVICE", Category == "904" ~ "State Employee Healthcare", Category == "910" ~ "LEGISLATIVE" , Category == "920" ~ "JUDICIAL" , Category == "930" ~ "ELECTED OFFICERS" , Category == "940" ~ "OTHER HEALTH-RELATED", Category == "941" ~ "PUBLIC SAFETY" , Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" , Category == "943" ~ "CENTRAL SERVICES", Category == "944" ~ "BUS & PROFESSION REGULATION" , Category == "945" ~ "MEDICAID" , Category == "946" ~ "CAPITAL IMPROVEMENT" , Category == "948" ~ "OTHER DEPARTMENTS" , Category == "949" ~ "OTHER BOARDS & COMMISSIONS" , Category == "959" ~ "K-12 EDUCATION" , Category == "960" ~ "UNIVERSITY EDUCATION", Category == "970" ~ "Local Govt Transfers", T ~ "CHECK ME!") ) %>% mutate(Category_name = str_to_title(Category_name)) #exp_long_nototals <- exp_long %>% filter(Category_name != "Totals") aggregated_totals_long <- rbind(rev_long, exp_long) ``` Change plots: ```{r} year_totals2 <- aggregated_totals_long %>% group_by(type, Year) %>% summarize(Dollars = sum(Dollars, na.rm = TRUE)) %>% pivot_wider(names_from = "type", values_from = Dollars) %>% rename( Expenditures = exp, Revenue = rev) %>% mutate(`Fiscal Gap` = round(Revenue - Expenditures)) %>% arrange(desc(Year)) # creates variable for the Gap each year year_totals2 # gap for FY22 changed to 2.3 billion annotation_billions <- data.frame( x = c(2004, 2017, 2019), y = c(60, 50, 10), label = c("Expenditures","Revenue", "Fiscal Gap")) fiscal_gap1 <- year_totals %>% ggplot() + geom_hline(yintercept = 0) + geom_line(aes(x = Year, y = Revenue/1000), color = "Black", lwd=1) + geom_line(aes(x = Year, y = Expenditures/1000, linetype = "dashed"), color = "red", lwd=1) + geom_line(aes(x = Year, y = `Fiscal Gap`/1000), color = "gray") + geom_text(data = annotation_billions, aes(x=x, y=y, label=label))+ theme_classic() + theme(legend.position = "none", #axis.text.y = element_blank(), #axis.ticks.y = element_blank(), axis.title.y = element_blank())+ scale_linetype_manual(values = c("dashed", "dashed")) + scale_y_continuous(limits = c(-20, 120), labels = comma)+ scale_x_continuous(limits=c(1998,2023), expand = c(0,0)) fiscal_gap1 fiscal_gap_droppedCURE <- year_totals2 %>% ggplot() + geom_hline(yintercept = 0) + geom_line(aes(x = Year, y = Revenue/1000), color = "Black", lwd=1) + geom_line(aes(x = Year, y = Expenditures/1000, linetype = "dashed"), color = "red", lwd=1) + geom_line(aes(x = Year, y = `Fiscal Gap`/1000), color = "gray") + geom_text(data = annotation_billions, aes(x=x, y=y, label=label))+ theme_classic() + theme(legend.position = "none",# axis.text.y = element_blank(), #axis.ticks.y = element_blank(), axis.title.y = element_blank() )+ scale_linetype_manual(values = c("dashed", "dashed")) + scale_y_continuous(limits=c(-20,120), labels = comma)+ scale_x_continuous(limits=c(1998, 2023), expand = c(0,0)) # # geom_smooth adds regression line, graphed first so it appears behind line graph # geom_smooth(aes(x = Year, y = Revenue), color = "gray", method = "lm", se = FALSE) + # geom_smooth(aes(x = Year, y = Expenditures), color = "rosybrown2", method = "lm", se = FALSE) + # # # line graph of revenue and expenditures # geom_line(aes(x = Year, y = Revenue), color = "black", size=1) + # geom_line(aes(x = Year, y = Expenditures), color = "red", size=1) + # geom_line(aes(x=Year, y = `Fiscal Gap`), color="gray") + # # geom_text(data= annotation, aes(x=x, y = y, label=label))+ # # # labels # theme_bw() + # scale_y_continuous(labels = comma)+ # xlab("Year") + # ylab("Millions of Dollars") + # ggtitle("Illinois Expenditures and Revenue Totals, 1998-2022") fiscal_gap_droppedCURE ``` Compare with and without federal COVID dollars: ```{r} library(gridExtra) cowplot::plot_grid(fiscal_gap1, fiscal_gap_droppedCURE, nrow=1, labels = c("With ARPA State CURE Funds", "Without ARPA State Cure Funds")) ``` **Revenue amounts in millions of dollars:** ```{r} rev_long %>% filter(Year == 2023) %>% #mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>% # select(-c(Year, `Total Expenditures`)) %>% arrange(desc(`Dollars`)) %>% ggplot() + geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`))+ coord_flip() + theme_bw() + labs(title = "Revenues for FY2023")+ xlab("Revenue Categories") + ylab("Millions of Dollars") ``` ## Forecasting attempt First images use revenue that includes all federal stimulus packages. Revenue projections are skewed heavily due to the large amount of covid money flowing in the past 2 years. ```{r eval=FALSE} ## Revenues year_totals2 <- year_totals2 %>% arrange(Year) #ts_rev <- year_totals %>% select(Year, Revenue ) %>% arrange(Year) tsrev <- ts(year_totals2$Revenue, start ="1998", frequency = 1) # yearly data # start(tsrev) # 1998, January # end(tsrev) ## 2022 # summary(tsrev) # plot(tsrev) # abline(reg=lm(tsrev~time(tsrev))) #### ARIMAs mymodel <- auto.arima(tsrev, seasonal = FALSE) # mymodel # ARIMA (0, 1, 0) with drift myforecastrev <- forecast(mymodel, h = 20) #plot(myforecastrev, xlab ="", ylab ="Total Revenue", main ="Chicago Revenue") #### revenue chart model_rev <- auto.arima(tsrev, seasonal = FALSE) forecast_rev <- forecast(model_rev, h = 20) q <- forecast(forecast_rev, h = 20) %>% autoplot() + ylab("Dollars (Millions)") + xlab("Year") + ggtitle("Forecasted Revenue") + theme_classic() + scale_y_continuous(labels = dollar ) summary(forecast_rev) # annotation <- data.frame( # x = c(2027, 2032), # y = c(200000, 300000), # label = c("$120 billion in 2027","$135 billion in 2032") # ) annotation <- data.frame( x = c(2020, 2032), y = c(150000, 200000), label = c("$120 billion in 2027","$135 billion in 2032") ) q+ geom_label(data = annotation, aes(x=x, y=y, label=label), size = 3) + labs(caption = "after dropping federal covid dollars") ## Expenditures tsexp <- ts(year_totals2$Expenditures, start = "1998", frequency = 1) model_exp<- auto.arima(tsexp, seasonal = FALSE) # model_exp # ARIMA (0,1,1) with drift forecast_exp <- forecast(model_exp, h = 20) #plot(forecast_exp, xlab ="", ylab ="Total Expenditures", main ="Chicago Expenditures") p <- forecast(model_exp, h = 20) %>% autoplot() + ylab("Dollars (Millions)") + xlab("Year") + ggtitle("Forecasted Expenditures") + theme_classic() + scale_y_continuous(labels = dollar ) summary(forecast_exp) annotation <- data.frame( x = c(2027, 2032), y = c(130000, 100000), label = c("$117 ± 20 Billion in 2027","$132 ± 26 Billion in 2032 ") ) p + geom_label(data = annotation, aes(x=x, y=y, label=label), size = 3) + labs(title = "Forecasted Expenditures", caption = "Projected values at 95% confidence interval. Dark blue represents 80% liklihood of falling with that range, light blue represents 95% liklihood of being in projected range.") ## Exp and Rev together autoplot(tsexp) + #geom_line(tsexp)+ #geom_line(aes(model_rev))+ autolayer(forecast_rev, series = "Revenue") + autolayer(forecast_exp, series = "Expenditure)", alpha = 0.5) + geom_line(year_totals, mapping= aes(x = Year, y = Revenue)) + guides(colour = guide_legend("Forecast")) + labs(title = "Forecasted Revenue and Expenditures", caption = "Revenue includes State and Local CURE Dollars") ``` Revenue forecasting using precovid trends: ```{r eval=FALSE, include=FALSE} # revenue using precovid trends tsrev <- ts(year_totals$Revenue, start ="1998", end = "2020", frequency = 1) # yearly data tsexp2019 <- ts(year_totals$Expenditures, start ="1998", end = "2020", frequency = 1) # yearly data #### revenue chart model_rev <- auto.arima(tsrev, seasonal = FALSE) forecast_rev <- forecast(model_rev, h = 23) c <- forecast(forecast_rev, h = 22) %>% autoplot() + ylab("Dollars (Millions)") + xlab("Year") + ggtitle("Forecasted Revenue") + theme_classic() + scale_y_continuous(labels = dollar ) summary(forecast_rev) annotation <- data.frame( x = c(2020, 2032), y = c(90000, 100000), label = c("$93 Billion in 2027","$104 Billion in 2032") ) c+ geom_label(data = annotation, aes(x=x, y=y, label=label), size = 3) + labs(title= "Revenue Forecasted using Pre-Covid Data", subtitle = "Own Source and Federal Revenues Combined") autoplot(tsexp2019) + #geom_line(tsexp)+ #geom_line(aes(model_rev))+ autolayer(forecast_rev, series = "Revenue") + autolayer(forecast_exp, series = "Expenditure)", alpha = 0.5) + geom_line(year_totals, mapping= aes(x = Year, y = Revenue)) + guides(colour = guide_legend("Forecast")) + labs(title = "Forecasted Revenue and Expenditures", subtitle = "Using Pre-Covid revenue data (ending in FY2020) with Actual 2022 expenditures") ``` ```{r eval=FALSE, include=FALSE} ### Federal Revenue fed_rev <- ff_rev %>% select(fy, rev_57, rev_58, rev_59) %>% mutate(fed_total = rev_57+rev_58+rev_59) fed_ts57 <- ts(fed_rev$rev_57, start ="1998", frequency = 1) # yearly data model_fed <- auto.arima(fed_ts57, seasonal = FALSE) forecast_fed <- forecast(model_fed, h = 23) fed57 <- forecast(forecast_fed, h = 20) %>% autoplot() + ylab("Nominal Dollars (Millions)") + xlab("Year") + ggtitle("Forecasted Federal Other Revenue") + theme_classic() + scale_y_continuous(labels = dollar ) fed57 fed_ts58 <- ts(fed_rev$rev_58, start ="1998", frequency = 1) # yearly data model_fed <- auto.arima(fed_ts58, seasonal = FALSE) forecast_fed <- forecast(model_fed, h = 23) fed58 <- forecast(forecast_fed, h = 20) %>% autoplot() + ylab("Nominal Dollars (Millions)") + xlab("Year") + ggtitle("Forecasted Federal Transfers for Transportation") + theme_classic() + scale_y_continuous(labels = dollar ) fed58 fed_ts59 <- ts(fed_rev$rev_59, start ="1998", frequency = 1) # yearly data model_fed <- auto.arima(fed_ts59, seasonal = FALSE) forecast_fed <- forecast(model_fed, h = 23) fed59 <- forecast(forecast_fed, h = 20) %>% autoplot() + ylab("Nominal Dollars (Millions)") + xlab("Year") + ggtitle("Forecasted Federal Medicaid Reimbursements") + theme_classic() + scale_y_continuous(labels = dollar ) fed59 ``` ```{r eval=FALSE, include=FALSE} fed_rev <- ff_rev %>% select(fy, rev_57, rev_58, rev_59) %>% mutate(fed_total = rev_57+rev_58+rev_59) fed_tstotal <- ts(fed_rev$fed_total, start ="1998", frequency = 1) # yearly data model_fed <- auto.arima(fed_tstotal, seasonal = FALSE) forecast_fed <- forecast(model_fed, h = 23) fedtotal <- forecast(forecast_fed, h = 20) %>% autoplot() + ylab("Nominal Dollars (Millions)") + xlab("Year") + ggtitle("Forecasted Federal Revenue WITHOUT Federal COVID Dollars", subtitle = "Sum of Transportation, Medicaid, and Other Federal Revenue") + theme_classic() + scale_y_continuous(labels = dollar ) fedtotal fed_tstotal <- ts(fed_rev$fed_total, start ="1998", end = "2020", frequency = 1) # yearly data model_fed2 <- auto.arima(fed_tstotal, seasonal = FALSE) forecast_fed2 <- forecast(model_fed2, h = 23) fedtotal2 <- forecast(forecast_fed2, h = 20) %>% autoplot() + ylab("Nominal Dollars (Millions)") + xlab("Year") + ggtitle("Forecasted Federal Revenue -- pre-COVID trends", subtitle = "Sum of Transportation, Medicaid, and Other Federal Revenue") + theme_classic() + scale_y_continuous(labels = dollar ) fedtotal2 autoplot(tsexp2019) + #geom_line(tsexp)+ #geom_line(aes(model_rev))+ autolayer(forecast_fed, series = "No Fed Stim Packages") + autolayer(forecast_fed2, series = "Pre-Covid Fed Rev)", alpha = 0.5) + geom_line(year_totals2, mapping= aes(x = Year, y = Revenue)) + guides(colour = guide_legend("Forecast")) + labs(title = "Comparison of Combined Federal Revenue without Stimulus Packages and Pre-COVID revenue trend" , subtitle = "Using Pre-Covid revenue data (ending in FY2020)") ``` Graphing the 3 federal revenue types together may be the most reliable since some COVID funding is still recorded in Federal Other and some are in other categories (like Disaster Response in FY2021). Need to look at more before using. ```{r drop-coviddollars3} # does not include rev_type == 58, medicaid dollars # covid_dollars <- rev_temp %>% filter(covid_dollars==1) # check what was dropped #covid_dollars %>% group_by(fy,rev_type) %>% summarize(receipts = sum(receipts)) %>% pivot_wider(names_from="rev_type", values_from = "receipts") rev_temp <- rev_temp %>% filter(covid_dollars==0) # keeps observations that were not coded as COVID federal funds ff_rev <- rev_temp %>% group_by(rev_type_new, fy) %>% summarize(sum_receipts = sum(receipts, na.rm=TRUE)/1000000 ) %>% pivot_wider(names_from = "rev_type_new", values_from = "sum_receipts", names_prefix = "rev_") ff_rev<- left_join(ff_rev, tax_refund) ff_rev <- mutate_all(ff_rev, ~replace_na(.,0)) ff_rev <- ff_rev %>% mutate(rev_02 = rev_02 - ref_02, rev_03 = rev_03 - ref_03, rev_06 = rev_06 - ref_06, rev_09 = rev_09 - ref_09, rev_21 = rev_21 - ref_21, rev_24 = rev_24 - ref_24, rev_35 = rev_35 - ref_35 ) %>% select(-c(ref_02:ref_35, rev_99, rev_76 ,ref_CHECK )) rev_long <- pivot_longer(ff_rev, rev_02:rev_78, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>% rename(Year = fy) %>% mutate(Category_name = case_when( Category == "02" ~ "INDIVIDUAL INCOME TAX" , Category == "03" ~ "CORPORATE INCOME TAX" , Category == "06" ~ "SALES TAX" , Category == "09" ~ "MOTOR FUEL TAX" , Category == "12" ~ "PUBLIC UTILITY TAX" , Category == "15" ~ "CIGARETTE TAXES" , Category == "18" ~ "LIQUOR GALLONAGE TAXES" , Category == "21" ~ "INHERITANCE TAX" , Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES" , Category == "27" ~ "CORP FRANCHISE TAXES & FEES" , Category == "30" ~ "HORSE RACING TAXES & FEES", # in Other Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" , Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped Category == "33" ~ "LOTTERY RECEIPTS" , Category == "35" ~ "OTHER TAXES" , Category == "36" ~ "RECEIPTS FROM REVENUE PRODUCING", Category == "39" ~ "LICENSES, FEES & REGISTRATIONS" , Category == "42" ~ "MOTOR VEHICLE AND OPERATORS" , Category == "45" ~ "STUDENT FEES-UNIVERSITIES", # dropped Category == "48" ~ "RIVERBOAT WAGERING TAXES" , Category == "51" ~ "RETIREMENT CONTRIBUTIONS" , # dropped Category == "54" ~ "GIFTS AND BEQUESTS", Category == "57" ~ "FEDERAL OTHER" , Category == "58" ~ "FEDERAL MEDICAID", Category == "59" ~ "FEDERAL TRANSPORTATION" , Category == "60" ~ "OTHER GRANTS AND CONTRACTS", #other Category == "63" ~ "INVESTMENT INCOME", # other Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped Category == "72" ~ "BOND ISSUE PROCEEDS", #dropped Category == "75" ~ "INTER-AGENCY RECEIPTS ", #dropped Category == "76" ~ "TRANSFER IN FROM OUT FUNDS", #other Category == "78" ~ "ALL OTHER SOURCES" , Category == "79" ~ "COOK COUNTY IGT", #dropped Category == "98" ~ "PRIOR YEAR REFUNDS", #dropped T ~ "Check Me!" ) )%>% mutate(Category_name = str_to_title(Category_name)) ff_exp <- exp_temp %>% group_by(fy, group) %>% summarize(sum_expenditures = sum(expenditure, na.rm=TRUE)/1000000 ) %>% pivot_wider(names_from = "group", values_from = "sum_expenditures", names_prefix = "exp_")%>% left_join(debt_keep_yearly) %>% mutate(exp_903 = debt_cost) %>% left_join(transfers) %>% mutate(exp_970 = exp_971 + exp_972 + exp_975 + exp_976) ff_exp<- ff_exp %>% select(-c(debt_cost, exp_971:exp_976)) # drop unwanted columns exp_long <- pivot_longer(ff_exp, exp_402:exp_970 , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>% rename(Year = fy ) %>% mutate(Category_name = case_when( Category == "402" ~ "AGING" , Category == "406" ~ "AGRICULTURE", Category == "416" ~ "CENTRAL MANAGEMENT", Category == "418" ~ "CHILDREN AND FAMILY SERVICES", Category == "420" ~ "COMMERCE AND ECONOMIC OPPORTUNITY", Category == "422" ~ "NATURAL RESOURCES" , Category == "426" ~ "CORRECTIONS", Category == "427" ~ "EMPLOYMENT SECURITY" , Category == "444" ~ "HUMAN SERVICES" , Category == "448" ~ "Innovation and Technology", # AWM added fy2022 Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", Category == "482" ~ "PUBLIC HEALTH", Category == "492" ~ "REVENUE", Category == "494" ~ "TRANSPORTATION" , Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" , Category == "557" ~ "IL STATE TOLL HIGHWAY AUTH" , Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", Category == "691" ~ "IL STUDENT ASSISTANCE COMM" , Category == "900" ~ "NOT IN FRAME", Category == "901" ~ "STATE PENSION CONTRIBUTION", Category == "903" ~ "DEBT SERVICE", Category == "904" ~ "State Employee Healthcare", Category == "910" ~ "LEGISLATIVE" , Category == "920" ~ "JUDICIAL" , Category == "930" ~ "ELECTED OFFICERS" , Category == "940" ~ "OTHER HEALTH-RELATED", Category == "941" ~ "PUBLIC SAFETY" , Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" , Category == "943" ~ "CENTRAL SERVICES", Category == "944" ~ "BUS & PROFESSION REGULATION" , Category == "945" ~ "MEDICAID" , Category == "946" ~ "CAPITAL IMPROVEMENT" , Category == "948" ~ "OTHER DEPARTMENTS" , Category == "949" ~ "OTHER BOARDS & COMMISSIONS" , Category == "959" ~ "K-12 EDUCATION" , Category == "960" ~ "UNIVERSITY EDUCATION", Category == "970" ~ "Local Govt Transfers", T ~ "CHECK ME!") ) %>% mutate(Category_name = str_to_title(Category_name)) #exp_long_nototals <- exp_long %>% filter(Category_name != "Totals") aggregated_totals_long <- rbind(rev_long, exp_long) ``` Change plots: ```{r} #| label: tbl-totals-without-FederalStimulus #| tbl-cap: Fiscal Gap without State CURE Federal Revenue year_totals2 <- aggregated_totals_long %>% group_by(type, Year) %>% summarize(Dollars = sum(Dollars, na.rm = TRUE)) %>% pivot_wider(names_from = "type", values_from = Dollars) %>% rename( Expenditures = exp, Revenue = rev) %>% mutate(`Fiscal Gap` = round(Revenue - Expenditures)) %>% arrange(desc(Year)) # creates variable for the Gap each year year_totals2 # gap for FY22 changed to ~2 or 3 billion ``` ```{r} #| label: fig-withandwithout-stateCURE #| fig-cap: Illinois Revenue Trend #| fig-subcap: #| - "With State CURE" #| - "Without State CURE" #| fig-cap-location: top annotation_billions <- data.frame( x = c(2004, 2017, 2019), y = c(60, 50, 10), label = c("Expenditures","Revenue", "Fiscal Gap")) fiscal_gap1 <- ggplot(data = year_totals, aes(x=Year, y = Revenue/1000)) + geom_recessions(text = FALSE)+ geom_hline(yintercept = 0) + geom_line(aes(x = Year, y = Revenue/1000), color = "Black", lwd=1) + geom_line(aes(x = Year, y = Expenditures/1000, linetype = "dashed"), color = "red", lwd=1) + geom_line(aes(x = Year, y = `Fiscal Gap`/1000), color = "gray") + geom_text(data = annotation_billions, aes(x=x, y=y, label=label))+ theme_classic() + theme(legend.position = "none", #axis.text.y = element_blank(), #axis.ticks.y = element_blank(), axis.title.y = element_blank())+ scale_linetype_manual(values = c("dashed", "dashed")) + scale_y_continuous(limits = c(-16, 120), labels = comma)+ scale_x_continuous(limits=c(1998,2023), expand = c(0,0)) fiscal_gap1 #ggsave(fiscal_gap1, file = "Figure7a-WithCURE.eps") fiscal_gap_droppedCURE <- ggplot(data = year_totals2, aes(x=Year, y = Revenue/1000)) + geom_recessions(text = FALSE)+ geom_hline(yintercept=0)+ geom_hline(yintercept = 0) + geom_line(aes(x = Year, y = Revenue/1000), color = "Black", lwd=1) + geom_line(aes(x = Year, y = Expenditures/1000, linetype = "dashed"), color = "red", lwd=1) + geom_line(aes(x = Year, y = `Fiscal Gap`/1000), color = "gray") + geom_text(data = annotation_billions, aes(x=x, y=y, label=label))+ theme_classic() + theme(legend.position = "none",# axis.text.y = element_blank(), #axis.ticks.y = element_blank(), axis.title.y = element_blank() )+ scale_linetype_manual(values = c("dashed", "dashed")) + scale_y_continuous(limits=c(-16,120), labels = comma)+ scale_x_continuous(limits=c(1998,2023),expand = c(0,0)) # # geom_smooth adds regression line, graphed first so it appears behind line graph # geom_smooth(aes(x = Year, y = Revenue), color = "gray", method = "lm", se = FALSE) + # geom_smooth(aes(x = Year, y = Expenditures), color = "rosybrown2", method = "lm", se = FALSE) + # # # line graph of revenue and expenditures # geom_line(aes(x = Year, y = Revenue), color = "black", size=1) + # geom_line(aes(x = Year, y = Expenditures), color = "red", size=1) + # geom_line(aes(x=Year, y = `Fiscal Gap`), color="gray") + # # geom_text(data= annotation, aes(x=x, y = y, label=label))+ # # # labels # theme_bw() + # scale_y_continuous(labels = comma)+ # xlab("Year") + # ylab("Millions of Dollars") + # ggtitle("Illinois Expenditures and Revenue Totals, 1998-2022") fiscal_gap_droppedCURE #ggsave(fiscal_gap_droppedCURE, file = "Figure7b-WithoutCURE.eps") ``` Compare with and without federal COVID dollars: ```{r include=FALSE} #| label: fig-both-graphs-comparison #| fig-cap: Comparison of revenue with and without federal State CURE funds. library(gridExtra) with_without_CURE<- cowplot::plot_grid(fiscal_gap1, fiscal_gap_droppedCURE, ncol=2, labels = c("With State CURE", "Without State Cure")) with_without_CURE #ggsave(plot = with_without_CURE, file = "Figure7.eps") ``` **Revenue amounts in millions of dollars:** ```{r} #| label: fig-revlong-bargraphs #| fig-cap: Comparison of revenue with and without federal State CURE funds. rev_long %>% filter(Year == 2023) %>% #mutate(`Total Expenditures`= sum(Dollars, na.rm = TRUE)) %>% # select(-c(Year, `Total Expenditures`)) %>% arrange(desc(`Dollars`)) %>% ggplot() + geom_col(aes(x = fct_reorder(Category_name, `Dollars`), y = `Dollars`))+ coord_flip() + theme_bw() + labs(title = "Revenues for FY2023")+ xlab("Revenue Categories") + ylab("Millions of Dollars") ``` ## All Categories with Totals ```{r} #| label: tbl-rev-tables-droppedCURE #| tbl-cap: Revenue without State CURE exp_totals <- ff_exp %>% rowwise() %>% mutate(exp_TOTALS = sum(across(exp_402:exp_970))) rev_totals <- ff_rev %>% rowwise() %>% mutate(rev_TOTALS = sum(across(rev_02:rev_78))) rev_long <- pivot_longer(rev_totals, rev_02:rev_TOTALS, names_to = c("type","Category"), values_to = "Dollars", names_sep = "_") %>% rename(Year = fy) %>% mutate(Category_name = case_when( Category == "02" ~ "INDIVIDUAL INCOME TAXES" , Category == "03" ~ "CORPORATE INCOME TAXES" , Category == "06" ~ "SALES TAXES" , Category == "09" ~ "MOTOR FUEL TAX" , Category == "12" ~ "PUBLIC UTILITY TAXES" , Category == "15" ~ "CIGARETTE TAXES" , Category == "18" ~ "LIQUOR GALLONAGE TAXES" , Category == "21" ~ "INHERITANCE TAX" , Category == "24" ~ "INSURANCE TAXES&FEES&LICENSES" , Category == "27" ~ "CORP FRANCHISE TAXES & FEES" , Category == "30" ~ "HORSE RACING TAXES & FEES", # in Other Category == "31" ~ "MEDICAL PROVIDER ASSESSMENTS" , Category == "32" ~ "GARNISHMENT-LEVIES" , # dropped Category == "33" ~ "LOTTERY RECEIPTS" , Category == "35" ~ "OTHER TAXES" , Category == "36" ~ "RECEIPTS FROM REVENUE PRODUCING", Category == "39" ~ "LICENSES, FEES & REGISTRATIONS" , Category == "42" ~ "MOTOR VEHICLE AND OPERATORS" , Category == "45" ~ "STUDENT FEES-UNIVERSITIES", # dropped Category == "48" ~ "RIVERBOAT WAGERING TAXES" , Category == "51" ~ "RETIREMENT CONTRIBUTIONS" , # dropped Category == "54" ~ "GIFTS AND BEQUESTS", Category == "57" ~ "FEDERAL OTHER" , Category == "58" ~ "FEDERAL MEDICAID", Category == "59" ~ "FEDERAL TRANSPORTATION" , Category == "60" ~ "OTHER GRANTS AND CONTRACTS", #other Category == "63" ~ "INVESTMENT INCOME", # other Category == "66" ~ "PROCEEDS,INVESTMENT MATURITIES" , #dropped Category == "72" ~ "BOND ISSUE PROCEEDS", #dropped Category == "75" ~ "INTER-AGENCY RECEIPTS ", #dropped Category == "76" ~ "TRANSFER IN FROM OUT FUNDS", #other Category == "78" ~ "ALL OTHER SOURCES" , Category == "79" ~ "COOK COUNTY IGT", #dropped Category == "98" ~ "PRIOR YEAR REFUNDS", #dropped Category == "TOTALS" ~ "Total" ) ) %>% select(-type, -Category) %>% # drop extra columns type and Category number group_by(Year, Category_name) %>% summarise(Dollars= round(sum(Dollars),digits=2)) %>% mutate(Category_name = str_to_title(Category_name)) # creates wide version of table where each revenue source is a column revenue_wide2 <- rev_long %>% pivot_wider(names_from = Category_name, values_from = Dollars) %>% # relocate("Other Revenue Sources **", .after = last_col()) %>% relocate("Total", .after = last_col()) ``` ```{r } exp_long <- pivot_longer(exp_totals, exp_402:exp_TOTALS , names_to = c("type", "Category"), values_to = "Dollars", names_sep = "_") %>% rename(Year = fy ) %>% mutate(Category_name = case_when( Category == "402" ~ "AGING" , Category == "406" ~ "AGRICULTURE", Category == "416" ~ "Central Management", Category == "418" ~ "CHILDREN AND FAMILY SERVICES", Category == "420" ~ "Community Development", Category == "422" ~ "NATURAL RESOURCES" , Category == "426" ~ "CORRECTIONS", Category == "427" ~ "EMPLOYMENT SECURITY" , Category == "444" ~ "Human Services" , Category == "478" ~ "HEALTHCARE & FAM SER NET OF MEDICAID", Category == "482" ~ "PUBLIC HEALTH", Category == "492" ~ "REVENUE", Category == "494" ~ "Transportation" , Category == "532" ~ "ENVIRONMENTAL PROTECT AGENCY" , Category == "557" ~ "Tollway" , Category == "684" ~ "IL COMMUNITY COLLEGE BOARD", Category == "691" ~ "IL STUDENT ASSISTANCE COMM" , Category == "900" ~ "NOT IN FRAME", Category == "901" ~ "State Pension Contribution", Category == "903" ~ "Debt Service", Category == "904" ~ "State Employee Healthcare", Category == "910" ~ "LEGISLATIVE" , Category == "920" ~ "JUDICIAL" , Category == "930" ~ "ELECTED OFFICERS" , Category == "940" ~ "OTHER HEALTH-RELATED", Category == "941" ~ "Public Safety" , Category == "942" ~ "ECON DEVT & INFRASTRUCTURE" , Category == "943" ~ "CENTRAL SERVICES", Category == "944" ~ "BUS & PROFESSION REGULATION" , Category == "945" ~ "Medicaid" , Category == "946" ~ "Capital Improvement" , Category == "948" ~ "OTHER DEPARTMENTS" , Category == "949" ~ "OTHER BOARDS & COMMISSIONS" , Category == "959" ~ "K-12 Education" , Category == "960" ~ "UNIVERSITY EDUCATION", Category == "970" ~ "Local Govt Revenue Sharing", Category == "TOTALS" ~ "Total") #,T ~ "All Other Expenditures **") ) %>% select(-type, -Category) %>% group_by(Year, Category_name) %>% summarise(Dollars= round(sum(Dollars),digits=2)) %>% mutate(Category_name = str_to_title(Category_name)) expenditure_wide2 <- exp_long%>% pivot_wider(names_from = Category_name, values_from = Dollars) %>% #relocate("All Other Expenditures **", .after = last_col()) %>% relocate("Total", .after = last_col()) ```